×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Microsoft: Office FAQ

Best of Excel

 How does VLOOKUP work?? by KenWright faq68-4743 Posted: 20 Jan 04 It's easy to get lost in a formula if you don't really understand what the formula is actuallydoing:-The VLOOKUP function essentially takes a value that you specify, whether it be a hardcodednumber/letter/text or a value within a cell reference, and then goes and looks it up in a table.It will look for that value in the leftmost column of the table, and either find it or the closestmatch, and will then return the corresponding value on the same row, in whatever column of thattable that you tell it to:-Example - With the following table    D    E1   1  0.202 10  0.253 20  0.304 30  0.355 40  0.40and with your value that you are looking up in say cell A1 (and let's assume it is 25 forexample).You can put a formula in pretty much any other cell, that says, take the value in cell A1 (25), goand look for it in the lefthand column of the table (D1:D5), and then when you have found it (orthe lowest closest number to it), go to the second column (or whichever one you specify if thereare more than 2) and give me the number that it is on the same row in that column.So, with the formula being =VLOOKUP(A1,D1:E5,2)  it will first take the value in A1 which is 25,then go look for it in the leftmost column (D) of your specified table (D1:E5), and it will try tofind that number.  Now it isn't there, so what it will do is look for the next lower closestnumber, which in this case will be 20, and the 2 in the formula says to go and get the value inthe 2nd column (E) in your table, that is on the same row as the 20.  That value in this case is0.3If you put the value 30 or 31 or 32 etc into A1 now, you will see the result of the formulachange, because now it will either find those numbers or the lower closest number (and in eachcase there it is 30), and will subsequently return 0.35 as the corresponding value.Just to show you how the 2 really works in that formula, if you added one more column to yourtable so that it looked like this:-    D    E      F1   1  0.20  0.152 10  0.25  0.253 20  0.30  0.354 30  0.35  0.455 40  0.40  0.55and you actually wanted the value from Col F, then you would simpl;y change the 2 in the formulato a 3 to signify the third column, eg:-=VLOOKUP(A1,D1:E5,3)With the examples already given, 25 in A1 would return 0.35, and 30/31/32 would return 0.45The one caveat to all of this (When getting the nearest number is OK) is that the data in yourleftmost column must be sorted in ascending order.There are times when you would only want it to give you a value if you had an exact match on thenumber, and in this instance you would simply add a 4th argument of 0 or FALSE to the formula,eg:-=VLOOKUP(A1,D1:E5,3,0)or=VLOOKUP(A1,D1:E5,3,FALSE)In these cases you do not need to have the data in your leftmost column sorted. Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!