You think you’ve entered the right formula and yet you get the dreaded #N/A error on the sheet…..well….here are some useful tips to get you through the vlookup blues!

=vlookup(value_to_match, range_to_find_this_value, which_column_to_return , try_to_approximate_if_no_match)

If you have imported data from a plain text file (a .txt file), your data may most likely have numbers which have been converted to string. Excel will still display the values as numbers but as they say, appearances can be deceptive. If you right click and then click Format cells option, you may find that the value you are trying to match with is formatting as a text. If you think that the problem was only with one cell, you can change the format to a number format right there (Just remember to press F2 and press enter after you’re done). But if the problem extends to other cells, you’d better off by converting the entire column. To do that select the entire column, click on Data in the menu bar, click on Text to Columns and press Finish. However, this works with only one column at a time.

If you had a large number of values to look up within the workbook and you copy paste the values from the first cell to the last, Excel will by default keep on changing the formula to keep track of references. So in this example, while the first two formulas may give correct results, by the time you reach the third, the range being referred to is no longer valid and results in an error value. Quick fix….change relative addresses to absolute ones…i.e. rather than using D4:G7 use $D$4:$G$7. A shortcut to do this is to highlight the “D4:G7” and press F4.

Sounds trivial, but oftentimes, when one has to vlookup across many columns, it is easy to lose a track of which column to return. Since the column are marked alphabetically (A to IV), one has to manually count and arrive at the column to return from the range.

If you have two datasets each with about 50,000 records and you wanted to do a match for each of the records, Excel may take time to resolve this. Depending upon your pc’s hardware, this can be instantaneous or can take a couple of seconds. So if the vlookup displays #N/A values all across, take a deep breath, relax and start counting…..1…..2…..3…..4…..

I do this all the time 🙂

Hi all.

Is there any way of applying vlookup function on multiple columns? Actually, I want more than one column to be transferred when a match occurs.

Thanks in advance,

August 29th, 2009 at 12:27 pmVag.

Hi Vag ! Welcome to da TaB

Do you mean that you would want to avoid writing multiple vlookups to return values from seperate columns? If you can give an example of how you’d like the vlookup to work in your case, that would be great.

You can try out the array version of the VLOOKUP formula:

= VLOOKUP (value_to_find,A1:C1,{1,2,3},FALSE) where A1:C1 is the range that you would like to return (each value in a seperate column. Even with this, the formula would have to be copied to as many cells as the numbe of values you would like to return. But as I said, and example can help me get a better idea.

Regards,

August 30th, 2009 at 12:44 am