VLOOKUP() - Get value based on another value

If we have a table of data, like the one shown bellow, and based on a value of that table want to return the value from another column we can use the VLOOKUP() function.

The syntax for this function is:


In our example, on cell B10 I've putted this formula:


First we have the lookup value that is the value that we want to check is it's on the table. In this example is the value on cell A10.

Then we have the table array. This is the range of cells where we want to check for the value on cell A10. In this case, we want to lookup the value on range A2:B7. If it finds the value of A10, it will the return the value referred by the col_index_num argument of the function. In this case we want to return the value from column 2 (the employee name).

The range_lookup argument is optional and it can have TRUE or FALSE value. If you put TRUE or omit this argument, it will return an approximated match. If it doesn't find the value of the lookup_value it will return the next largest value that is less than the lookup_value. If you put FALSE, it will find an exact match and if it doesn't find one it will return the error value #N/A.

So, on this example, we want to find the value 32893 and get the corresponding value from column 2, in this case the returned value will be James Watson.

If the value specified on the lookup_value is not found, and the range_lookup is FALSE, then the formula will return a #N/A error value. We can change our formula so that it doesn't display this error with this formula:


In this case we use two more functions, the IF and ISNA. I will not explain the IF function in this article, will handle this function on a separated article on the future.

The ISNA() function checks if the VLOOKUP() function returns a #N/A error value. So this formula will check if the VLOOKUP() function returns #N/A. If it does, the cell will be empty. If it doesn't it will show the result of the VLOOKUP function.

20 comentários:

Anonymous said...

Thank you for posting this! It was tremendously helpful!

Anonymous said...

it is helpful to me.
Thanks a lot!

Anonymous said...

Thanks for posting this. I got me out of a large whole. ;-)

Alice In Wonderland said...
This comment has been removed by the author.
Anonymous said...

Thank you, this did exactly what I was looking for.

Anonymous said...

Thanks a Lot !!! its very much useful & nicely explained here..

Anonymous said...

I have been trying to use your example to work in my spreadsheet but have not been able to get it to work out. Would it be possible for you to e-mail me your e-mail address so as to forward you my problem in hopes of them being corrected? My e-mail is (shopbell@yahoo.com)

Thank you very much.

signed; desperate employee

jppinto said...

You can find my email on the Contacts ab of the page.

Anonymous said...

How do you adjust this so it looks for the value entered across multiple worksheets?

Anonymous said...

Thank You Very Much.
This is very helpful in my work.
Pratik A. Parmar

Anonymous said...

hi, with due respect, in the abocve example, does the formulae work? because what you are returning is a text string. and vlookup formulae which you have mentioned does not return text string as answers. I may be wrong but would like you to check it up.

Anonymous said...

very helpful and clear, thanks much

Anonymous said...

It was really helpful to mams Prabha ... Thank you ...

Anonymous said...

don't forget the Zoolander problem.

Anonymous said...

Hi, it worked for me
but I'm looking for a number in a column G. This number should be (or not) on column A, I don't want the function looking for this number in column B (luckily the numbers were not on column B that's why the formula above worked for me) but I need a formula (or function) that look into A but display the value in B.


Brianne Binelli said...

I need to lookup what data I entered in column A and lookup the equivalent to that data from another sheet.

column a: number

Column A: Number Column B. Return name.

Can someone help me with this, very very new to exel.
Brie a

Anonymous said...

THANK YOU... i just created my first successful VLOOKUP!!

Anonymous said...

And I THANK YOU too. Clearest explanation I can find after several hours!

Dipak Bawankule said...

Very Helpful . Keep us posted.

Anonymous said...

Very good detailed information on the function. But, unless I was doing something daft, it was returning "n/a" for me when my lookup value (decimal) was in col B and the found value (date) required was in col A. Further investigation tells me you can only lookup the value from the 1st col of the table_array?