Excel VLOOKUP Formula Example

In the list below we want to calculate a commission in column F for each builder. However, each builder has a specific commission rate they are entitled to. Thankfully we have this information in a table to the right, and this is where we give VLOOKUP the opportunity to strut its stuff.

Excel VLOOKUP Function Syntax

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

And to translate it into English it would read:

VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)

Let’s make it even clearer by applying it to our example:

VLOOKUP(find the name Doug from cell B2, in the Commission Rates table H2:I9, return the value in column 2 of the table, but only return a value if you find the exact name Doug in the Commission Rates table, otherwise give me an error)

Important Points

‘Return the value in column 2 of the table’is referring to the column number in the table H2:I9, not the column number of the spreadsheet. The information we want returned is the percentage rate, and it is in the second column of the Commission Rates table.

‘Only return a result if you can match the value exactly’is telling Excel that we only want information returned if it matches our criteria exactly. i.e. Find Doug in our Commission Rates Table, and if you can’t find Doug, give me an error. The error displayed will be #N/A.

On the other hand, if we told Excel it was ok to not find an exact match, it would return the next best result. i.e. If Doug wasn’t in our Table Excel would return the next best result. In this example we wouldn’t want it to do that, but this option is handy in other situations which we’ll cover in another tutorial.

OK, now that’s clarified, our formula in column F for the above example would be:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)

Note: Where ‘FALSE’ is telling Excel we want it to find an Exact Match only.

Our Excel table would then look like this with the VLOOKUP formula in column F:

You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table H2:I9. This is called an absolute reference. Absolute references allow us to quickly copy the formula down column F without Excel dynamically updating the table range as we copy.

How can we make this VLOOKUP formula even better?

By calculating the commission $ amount in one step in column F. Let’s say commission is calculated as Total $k x Commission %, our formula in cell F2 would read:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)*E2

And in seconds we can have hundreds of calculations done!

Rules, Common Mistakes and Troubleshooting!

VLOOKUP formulas read from left to right. You must have the information you are looking up (in our example Doug in the Commission Rates Table), in a column to the left of the information you want returned. Which in our example is the ‘percentage rate’. i.e. it has to go ‘Doug’, then ‘% rate’. Excel wouldn’t be able to find it if it went ‘% Rate’ then ‘Doug’.

You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.

The ‘Table’ you are looking up can be in the same spreadsheet. Or a different sheet in the same workbook. Or in a different workbook altogether.

The table doesn’t have to be sorted in any particular order, but you must not have duplicates. Unless the information on each duplicate is exactly the same. For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, VLOOKUP would return the rate on the first instance of Doug.

The formula isn't case sensitive, so 'Doug' could be 'doug' or 'Doug', in either column B or the table.

What does it mean when my VLOOKUP returns a #N/A? It means Excel can't find the value you're trying to look up in your table. If you get this, but you can ‘plain as day’ see it's there in the table, then it’s likely you’ve got one prefixed with an apostrophe. To check this go to each cell you're referencing and look in the formula bar and see if there is an apostrophe in either cell ‘. You can only see the apostrophe from the formula bar. See example below.

Basically, Excel reads text prefixed with an apostrophe as different to text without. Even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't. The quickest way to get rid of the apostrophes is to do ‘Text to Columns’. Or run it through the VALUE function, which converts numbers formatted as text to actual numbers.

This formula works the same in all versions of Microsoft Excel.

VLOOKUP is a fairly basic formula, but its applications are vast.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Thanks a lot for the ebook Tips & Tricks. Unfortunately, it’s not as what I wanted. I am looking for help to do with lookup database pictures. The result from vlookup data from the table as the result should be picture as wanted. Although, I have some examples from the internet but Sorry I’m not satisfied with them. I hope might be you would help me, please. Thanks a lot.

Hi Emmanuel,
Can you please provide a sample file and detailed descriptions for what you are trying to achieve? You can use our Help Desk System to upload the file, please create a new ticket.
Cheers,
Catalin

Hi Kamal,
You can use this formula in Z1, and copy it down to Z3:
=LARGE(IF($A$1:$A$21=”A”,$B$1:$B$21,0),ROW(A1)) entered with Ctrl+Shift+Enter (it’s an array formula)
For largest B projects: use this formula in Y1, copied to Y3:
=LARGE(IF($A$1:$A$21=”B”,$B$1:$B$21,””),ROW(A1)) entered with Ctrl+Shift+Enter (it’s an array formula)
Cheers,
Catalin

Hi Kamal,
Please describe from the beginning the entire problem, because any little detail can change the approach, and the solution may be totally different.
Try this file from our OneDrive folder. The solution works fine if you do not have duplicates, at least for the highest 3 numbers.
Cheers,
Catalin

Hi Mynda, I am trying to do some analysis on a s/s. I have a worksheet which has data arranged in columns, such as instance, location, months. I have another s/s with new data for the current month. I am trying to compare data from the current month to the existing s/s and if data (instance and locations) match, then add this new data as a new column to the existing s/s. The instances column should have unique data, but if there are data in the new or existing s/s that do not match, then append them at the bottom of the existing s/s. What formula should I use? Thanks for your help.

Hi,
VLOOKUP is an Excel Function that is used within tables to help filter through large volumes of data and
select the appropriate data based on given conditions. The VLOOKUP formula would automatically look through the list of your Objects and pick out
the corresponding data.

The function is very well described in this tutorial , please take your time to understand the explanations, you can also download an example workbook, the link for download is at the end of the tutorial.
Catalin

Can solve this puzzle from a spread sheet from 1996 deals with gas processing Question on VLOOKUP Function This is part of a spread sheet
Amine Treater
Amine type (MEA, DEA, MDEA) DEA Typical Amine solution properties are shown below:

Intermediate Calculation Results
CO2 and H2S to be removed 60.84 lb-moles/hr
Solution specific gravity 1.04 VLOOKUP(UPPER($C$27),PROP,4,FALSE)
Amine molecular weight 105.14 =VLOOKUP(UPPER($C$27),PROP,5,FALSE) Do you know how cell DEA($C$27) at the top of the sheet is referenced to the two cells for Specific Gravity 1.04 & Molecular Weight 105.14 is nested or referenced?
Can’t find the chart UPPER on the spread sheet. PROP is Amine Look Up Table, immediately above the text.
Could send he spread sheet to you.
Regards….

Firstly thanks for an awesome site. I have intermediate excel skills, but you’re explanations have made learning new formulas really easy!

In relation to VLOOKUP – I’m using it in a training register to confirm who has completed which training on what date. The column with the formula is formatted for dates as dd/mm/yyyy. Some people haven’t completed the training yet, and rather than leaving the cell blank it gives the result 0/01/1900. This is the formula as I’ve put it in the sheet =VLOOKUP($A5,Induction!$A:$C,3,FALSE). Is there any thing I can do to make it leave the cell blank if the reference is blank or perhaps a different formula I could use

Dear,
I need a help with Excel formula, probably it’s easy but I can’t get it!
I have a table of one month and in 2right columns 2 figures, the form is used for account.
I made a box with Now() and want to make a formula to take data from table for present day. So first have to confirm same date as today from table and then to take data from 2right columns.
Thank You Very Much in Advance
Pero

Hi
I have two tables one with colum name as “login id” and another with “computer name” and same colum in other sheet. i just want to compair login id and copy respective computer name to it. i tryed following funtion
=VLOOKUP(A2,sheet2!A:b,2,0) result is #N/A

I have used this lookup formula for years with complete confidence.
=if(vlookup(cell,range,1)=cell,vlookup(cell,range,Column # to be returned),” “). This returns an exact match if found and a blank cell if not.

But I have run into a problem, my formula is not returning anything on some newly added items in the lookup range. The item are still in sorted order and still in the lookup range. The format of the information is a match. Have you come across this?

Hi Mynda. The best for you in 2013… As usual, finding the best answers here… Excellent job, really.

Mynda, I’m having a trouble. Your explanation was great on the Vlookup formula syntaxis, but I was just wondering if the “col_index_num” requirement would look into rows instead of columns…. How would achieve that? I guess this function isn’t going to work for me, since I need to return a value that’s five rows under the respective “lookup_value” reference, and not to the side…

Is there an equivalent to this formula, but reading from the top of a table to its bottom?

This is excellent. I have been trying to teach myself vlookup today using the Help option and it didn’t help! What a difference it makes to actually see the data and have it so clearly explained. You have de-mystified vlookup for me and I’m now looking forward to getting to work tomorrow to try it out on my spreadsheet! Thank you very much.

Awesome breakdown of the VLOOKUP formula. This saved my day at work. I came here because it seemed intimidating but after seeing the tutorial, I now scoff at its fear-factor, lol. You also have a hot voice. Extra brownie points for you! : )

Mynda,
Is there any way to have a vlookup formula present where if the fields are left blank you can have a return of 0 instead of N/A? I tried including a blank line item in my chart with a 0 value but it still comes back as N/A and is killing my totals.
I can show you my work so far but it is getting messy!

Related to Chris K’s query above, VLOOKUP puts a value 0 if the corresponding value cell (column index cell) is empty. It messes up my other calculations. Is there any way to get VLOOKUP with help of other function to return a particular value (say “Empty”) if the cell is empty.

I understand we can use IFERROR with VLOOKUP the lookup value can’t be found. But I am interested when the lookup value is found but the corresponding column index cell for that lookup value is empty.

I’m currently using vlookup in my work. Now I have an problem to solve. I have two reports that I need to work with. One FY12 customer sales with part numbers. The other FY13July customer sales with part numbers. I want to put the FY13July sales number in a column on the FY12 File and add to each month end so we have a running total. The problem is the look up value is not unique. Many customer buy the same part #. Is there a way to use 2 cells as the look up value? (acct # & part #) Thanks for your help.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?