|
|
||
|
|
|
Menu Bar (Click
on the buttons below to rediscover Excel Spreadsheet Tips) |
| What
can be done with VLOOKUP |
| 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 in my 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 5th argument 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. |
The VLOOKUP function in ExcelIf 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 calculate the 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 is as
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
EXACT match 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. You can also use the address of a cell in
which you change the value as first argument in the formula (ex:
J20) 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. |
|
Menu Bar (Click
on the buttons below to rediscover Excel Spreadsheet Tips) |
|
|
||
|
|