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:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

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

=VLOOKUP(A10,$A$2:$B$7,2,FALSE)

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:

=IF(ISNA(VLOOKUP(A10,$A$2:$B$7,2,FALSE)),"",VLOOKUP(A10,$A$2:$B$7,2,FALSE))

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.

## 19 comentários:

Thank you for posting this! It was tremendously helpful!

it is helpful to me.

Thanks a lot!

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

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

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

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

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

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

Thank You Very Much.

This is very helpful in my work.

Pratik A. Parmar

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.

very helpful and clear, thanks much

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

don't forget the Zoolander problem.

Hi, it worked for me

=(VLOOKUP(G2,A:B,2,FALSE))

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.

Thanks

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

column a: number

sheet2

Column A: Number Column B. Return name.

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

thanks.

Brie a

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

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

Very Helpful . Keep us posted.

Post a Comment