Excel VLOOKUP  Function

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel Chapter 19C of 24: Excel VLOOKUP Function

The VLOOKUP function in Excel searches for a value in the leftmost column of a table and returns the value on the same row of another column to the right. So VLOOKUP looks up for " John" in column A and his address, his age or his balance appear in either columns B, C or D.

Here are usual questions from users:

1- What if the lookup column in  my table is not the leftmost column?

With VLOOKUP function it is complicated. The table needs to be reorganize to make the lookup column the left most column of the table. With INDEX/MATCH the lookup column can be anywhere.

2- What if the values in my lookup column are not in alphabetical order?

With INDEX/MATCH instead of VLOOKUP it doesn't matter. With VLOOKUP you need to add a 5th  argument to your formula (FALSE). This means that each time a new row is added   the table MUST be re-sorted.

3- Can 2 criteria be used with VLOOKUP? For example, can I find John (A) Baxter (B)'s address ( C) or can I find the price (D)  of a 1978 (A), Chevrolet ( B), Malibu (C).

Yes, but it is complicated. Create a new column in which the values of the criteria columns are concatenated and use this new column as criteria (first and last name or year, make and model). Remember that this new column must be the leftmost column of the table.

No need to create a new column with SUMPRODUCT or INDEX/MATCH in array format.

4- In a sales database can I sum all the sales (E) of one product (B) with VLOOKUP?

No. Use SUMPRODUCT. For example, if in A you have dates, in B store numbers,   in C products and in D amounts,   you can with SUMPRODUCT sum the sales of a product, in a certain store and on a certain date.

VLOOKUP is a function inherited from Lotus-123. There is much better in Excel, more powerful and less limited, it is INDEX/MATCH. INDEX/MATCH replaces all lookup functions (VLOOKUP, HLOOKUP and LOOKUP).

The VLOOKUP function in Excel

This 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 VLOOKUP looks up for "John" in column A and get shis address, his age or his balance that are in columns 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 your formula is looking up through MUST BE IN ASCENDING ORDER (a, b, c, d) unless the fifth argument is set to FALSE.

Here is a basic formula: =VLOOKUP("John" ,A1:G32,2, FALSE)

The syntax for these formulas is  as follows the first argument ("John" ) is what VLOOKUP is 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 the fifth argument is omitted or set to 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 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.

Use   the address of a cell in which the value as first argument in the formula (ex: J20) is changed
=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 VLOOKUP is looking up within are in ascending order.

VLOOKUP is always looking up within the first column of the table that is submitted as second argument. If column C is to be the look up column change the second argument to C1:G32.
=VLOOKUP(J20,C1:G32,2, FALSE).

You resisted to the switch from Lotus123 to Excel but you would never go back. You resisted to the switch from Word Perfect  to Word  but you would never go back. Try SUMPRODUCT and INDEX/MATCH  and you will never go back to VLOOKUP.

 

Next Chapter: Math and Trig Functions and Formulas in Excel

Excel Table of Contents
or use the site map link below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel VLOOKUP  Function