The VLOOKUP function searches for a value in the left-most column of a table and returns the value on the same row of another column to the right. So you look up for "John" in column A and you get his address, his age or his balance that are in either columns B, C or D. It is not a bad function but there is much, much better.
VLOOKUP is an obsolete function inherited from Lotus-123. There is much better in Excel, more powerful and less limited, it is INDEX/MATCH. INDEX/MATCH replaces all lookups functions (VLOOKUP, HLOOKUP and LOOKUP). Here are usual questions from VLOOKUP users:
1- What if the look-up column inmy table is not the left-most column?
If you insist on using the VLOOKUP function it is complicated. You need to reorganize your table and make the look-up column the left most column of the table. If you are using INDEX/MATCH the look-up column can be anywhere.
2- What if the values in my lookup column are not in alphabetical order?
If you are using INDEX/MATCH instead of VLOOKUP it doesn't matter. If you use VLOOKUP you need to add a 5thargument to your formula (FALSE). This means that each time that you add a new row in your table you MUST re-sort it.
3- Can 2 criteria be used with VLOOKUP? For example, can I find John (column A) Baxter (column B)'s address (column C) or can I find the price (column D)of a 1978 (column A), Chevrolet (column B), Malibu (column C).
Yes, but it is complicated. You create a new column in which you concatenate the value of the criteria columns and you use this new column as criteria (first and last name or year, make and model). Remember that you need this new column to be the left-most column of the table.
If you are searching for a price (example 2) you just use a simple SUMPRODUCT formula with all three criteria. If you are looking for an address you will use INDEX/MATCH in array format.
4- In a sales database can I sum all the sales (column E) of one product (column B) with VLOOKUP?
No. Use SUMPRODUCT. For example, if in column A you have dates, in column B store numbers, in column C products and in column D amounts, you can with SUMPRODUCT sum the sales of a product, in a certain store and on a certain date. SUMPRODUCT is Excel's best kept secret and it is the ultimate tool to automate reports.
If you have read what is aove, I am surprised that you go on. But if you really insist....
The VLOOKUP function searches for value in the left-most column of a table and returns the value on the same row of another column to the right. So you look up for "John" in column A and you get his address, his age or his balance that are in columns in column B, C or D.
VLOOKUP in Excel is used in two situations:
- On an invoice or other documents you enter the name of a client and in the next few cells an Excel VLOOKUP formula brings in the address 1, address 2, the city, State and ZIP Code. Less manual entries less mistakes. On the same invoice you type in the product number and in other cells its description, unit and unit price are carried over by Excel VLOOKUP's.
- You have a set of sales data and you want to subtotal sales by cities. Unfortunately the city in which the client lives in not part of the set of data. This information exists in another set of data that you import on sheet 2. With Excel VLOOKUP you can bring the name of the city within your first set of data on sales and calculatethe subtotals by city.
IMPORTANT NOTE: The values that you are looking up through MUST BE IN ASCENDING ORDER (a, b, c, d) unless you use the fifth argument FALSE.
Here is a basic VLOOKUP formula: =VLOOKUP("John",A1:G32,2, FALSE)
The syntax for these formulas isas follows; the first argument ("John") is what you are looking for, the second argument (A1:G32) means to search a value in the table A1 to G32 looking up for "John" in column A, the third argument (2) is the column from which the answer is extracted, and the fifth argument (FALSE) tells Excel that you are looking for an EXACTmatch and not the next lower value. If you omit the fifth argument or use TRUE the formula will return the answer for the next lower value if it doesn't find EXACTLY what you are looking for and if the values are sorted in ascending order. For example if you are looking up for John and the formula only finds Albert and Susan it will return the answer for Albert.
=VLOOKUP("John",A1:G32,2, FALSE) looks for "John" in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.
You can also use the address of a cell in which you change the value as first argument in the formula (ex: J20)
=VLOOKUP(J20,A1:G32,2, FALSE) looks for whatever value is in cell J20 in the first column (A) of the range (A1:G32) and returns the value of the cell in the 2nd column (B) same row.
Remember to ALWAYS use "FALSE" as fifth argument and to make sure that the values in the column that you are looking up within are in ascending order.
VLOOKUP is always looking up within the first column of the table that you submit as second argument. If you want the column C to be the look up column you need to change the second argument to C1:G32.
=VLOOKUP(J20,C1:G32,2, FALSE).
