Categorized | excel formula, excel tips
Tags |

VLOOKUP Function Error

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)

(Delimited) text being compared to numbers

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.

Copying the same cell across the column

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.

Wrong column number given as argument

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.

Large data set taking time to resolve

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…..

VLOOKUP Spelled wrong (VLLOKUP)

I do this all the time :-)

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Vag wrote:

    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,

  2. Ajay wrote:

    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.



Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel