Aug
27

Excel VLOOKUP not working - solutions for N/A, NAME and VALUE errors

The tutorial explains how you can quickly cope with VLOOKUP not working problems in Excel 2013, 2010, 2007 and 2003, troubleshoot and fix common errors and overcome VLOOKUP's limitations.

In the last few articles, we have explored different aspects of the Excel VLOOKUP function. If you have been following us closely, by now you should be an expert in this area : )

However, it's not without a reason that many Excel specialists consider VLOOKUP to be one of the most intricate Excel functions. It has a ton of limitations and specificities, which are the source of various problems and errors.

Excel VLOOKUP not working: #N/A, #VALUE, #NAME errors In this article, you will find simple explanations of VLOOKUP's #N/A, #NAME and #VALUE error messages as well as solutions and fixes. We will start with the most frequent cases and most obvious reasons why vlookup is not working, so it might be a good idea to check out the below troubleshooting steps in order.

Fixing VLOOKUP N/A error in Excel

In Vlookup formulas, the #N/A error message (meaning "not available") is displayed when Excel cannot find a lookup value. There can be several reasons why that may happen.

1. A typo or misprint in the lookup value

It's always a good idea to check the most obvious thing first : ) Misprints frequently occur when you are working with really large data sets consisting of thousands of rows, or when a lookup value is typed directly in the formula.

2. #N/A in approximate match VLOOKUP

If you are using a formula with approximate match (range_lookup argument set to TRUE or omitted), your Vlookup formula might return the #N/A error in two cases:

  • If the lookup value is smaller than the smallest value in the lookup array.
  • If the lookup column is not sorted in ascending order.

3. #N/A in exact match VLOOKUP

If you are searching with exact match (range_lookup argument set to FALSE) and the exact value is not found, the #N/A error is also returned. See more details on how to properly use exact and approximate match VLOOKUP formulas.

4. The lookup column is not the leftmost column of the table array

As you probably know, one of the most significant limitations of Excel VLOOKUP is that it cannot look to its left, consequently your lookup column should always be the left-most column in the table array. In practice, we often forget about this and end up with VLOOKUP not working because of the N/A error.
The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N/A error.

Solution: If it is not possible to restructure your data so that the lookup column is the left-most column, you can use a combination of Excel's INDEX and MATCH functions, as a more versatile alternative to VLOOKUP. You will find the detailed info and a formula example in this tutorial - INDEX / MATCH formula to lookup values to left.

5. Numbers are formatted as text

Another source N/A errors in VLOOKUP formulas is numbers being formatted as text, either in the main or lookup table.

This usually occurs when you import data from some external database or if you've typed an apostrophe before a number to indicate a leading zero.

The most obvious indications of numbers being formatted as text are shown in the screenshot below.
The indications of numbers being formatted as text in Excel

The numbers can also be stored in the General format. In this case, there's only one noticeable sign - numbers get aligned to the left side of a cell, while numbers stored as numbers are aligned to the right by default.

Solution: If this is just a single number, simply click on the error icon and choose "Convert To Number" from the context menu.
Converting numbers formatted as text to the normal numbers format.

If multiple numbers are affected, select them all, right-click the selection, then choose Format Cells > Number tab > Number and click OK.

6. Excess leading or trailing spaces

This is the least obvious cause of the Vlookup N/A error because a human eye can hardly spot those extra spaces, especially when working with big tables where most of the entries are below the scroll.

Solution 1: Extra spaces are in the main table (with VLOOKUP formulas)

If excess spaces occur in your main table, you can ensure the correct work of your Vlookup formulas by wrapping the lookup_value argument with the TRIM function:

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
If extra spaces occur in the main table, wrapping the lookup value with the TRIM function.

Solution 2: Extra spaces are in the lookup table (lookup column)

If extra spaces occur in the lookup column, there is no easy way to avoid VLOOKUP #N/A errors. Instead of VLOOKUP, you can use an array formula with a combination of INDEX / MATCH and TRIM functions:

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))

Since this is an array formula, don't forget to press Ctrl + Shift + Enter rather than a usual Enter keystroke to properly complete it:
If extra spaces occur in the lookup table, use INDEX / MATCH / TRIM instead of VLOOKUP.

For more information about using INDEX / MATCH in Excel, please check out this tutorial: INDEX & MATCH in Excel - a better alternative to VLOOKUP.

Tip. A quick alternative to complex INDEX / MATCH formulas is running the Trim Spaces for Excel add-in that will eliminate excess spaces both in the lookup and main tables in seconds, making your VLOOKUP formulas error-free. This is a free tool and you can find the download link on the above page.

#VALUE error in VLOOKUP formulas

In general, Microsoft Excel displays the #VALUE! error if a value used in the formula is of a wrong data type. In respect to VLOOKUP, there are two common sources of the VALUE! error.

1. Lookup value exceeds 255 characters

Please be aware that the VLOOKUP function cannot look up values containing 256 characters or more. If your lookup values exceed this limit, you will end up having the VALUE error:

The VALUE error in VLOOKUP when a lookup value exceeds 255 characters.

Solution: Use an analogous INDEX /MATCH formula instead. In the above example, the following INDEX / MATCH function works perfectly:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))

If a look up value contains 256 characters or more, use INDEX / MATCH instead of VLOOKUP

You can learn more about using INDEX / MATCH in Excel in this tutorial.

2. Full path to the lookup workbook is not supplied

If you are pulling data from another workbook, you have to include the full path to that file. More precisely, you have to enclose the workbook's name (including the extension) in square brackets [], and then specify the sheet's name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces.

Here's the structure of the complete formula to do a vlookup from another workbook:

=VLOOKUP(lookup_value, '[workbook name]sheet name'!table_array, col_index_num, FALSE)

A real formula might look similar to this:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

The above formula will search for the value of cell A2 in column B of Sheet1 in the "New Prices" workbook, and return a matching value from column D.

If any element of the path is missing, your VLOOKUP formula won't work and return the #VALUE error (unless the lookup workbook is currently open).

For more info about VLOOKUP formulas referencing another Excel file, please check out this tutorial: How to do vlookup from a different workbook.

3. The col_index_num argument is less than 1

It's hard to imagine a situation when someone would want to enter a number less than "1" to specify the column to return values from. Though, it may happen if this argument is returned by some other Excel function nested in your Vlookup formula.

So, if the col_index_num argument happens to be less than 1, your Vlookup formula will return the #VALUE! error too.

If the col_index_num argument is greater than the number of the columns in the specified table array, Vlookup formulas return the #REF! error.

VLOOKUP #NAME error

This is the easiest case - the NAME error appears if you've accidentally misspelled the function's name.

The solution is obvious - check the spelling : )

Excel VLOOKUP not working (limitations, issues and solutions)

Apart from having a fairly complicated syntax, VLOOKUP has arguably more limitations than any other Excel function. Because of these limitations, seemingly correct Vlookup formulas might often deliver results different from what you expect. Below you will find solutions for a few common scenarios when VLOOKUP fails.

1. VLOOKUP is case-insensitive

The VLOOKUP function does not distinguish case and teats lowercase and UPPERCASE characters as identical. So, if your table includes several similar entries that differ in the uppercase or lowercase chars only, the Vlookup formula will return the first found value regardless of the case.

Solution: Use another Excel function that can do a vertical lookup (LOOKUP, SUMPRODUCT, INDEX / MATCH) in combination with the EXACT function that can match case. You can find the detailed explanations and formula examples in this tutorial - 4 ways to do a case-sensitive vlookup in Excel.

2. VLOOKUP returns the first found value

As you already know, Excel VLOOKUP returns the first value it finds in the return column that matches the lookup value. However, you can force it to bring the 2nd, 3rd, 4th or any other occurrence you want. If you need to get all duplicate occurrences, you will have to use a combination of the INDEX, SMALL and ROW functions.

Solutions: Formula examples are available for download here:

3. A new column was inserted or removed from the table

Regrettably, VLOOKUP formulas stop working every time when a new column is deleted from or added to a lookup table. This happens because the syntax of the VLOOKUP function requires that you supply the entire table array as well as a certain number indicating which column you want to return the data from. Naturally, both the table array and the return column's number change when you remove an existing column or insert a new one.

Solution: INDEX / MATCH comes to the rescue again : ) In INDEX & MATCH formulas, you specify the lookup and return columns separately, and as a result you can delete or insert as many columns as you want without worrying about updating every associated vlookup formula.

4. Cell references changed when copying the formula to other cells

The heading gives an exhaustive explanation of the problem, right?

Solution: Always use absolute cell references (with the $ sign) in table arrays, e.g. $A$2:$C$100 or $A:$C. In the formula bar, you can quickly switch between different reference types by pressing F4.

VLOOKUP with IFERROR / ISERROR

If you do not want to intimidate your users with all those N/A, VALUE or NAME error messages, you can return a blank cell instead, or display your own message. You can do this by wrapping your VLOOKUP formula in the IFERROR function in Excel 2013, 2010 and 2007 or with IF / ISERROR in earlier Excel versions.

Using VLOOKUP with IFERROR

The syntax of the IFERROR function is simple and self-explanatory : )

IFERROR(value,value_if_error)

Meaning, you enter the value to check for an error in the 1st argument, and in the 2nd argument you specify what to return if an error occurs.

For example, the following IFERROR / VLOOKUP formula returns a blank cell when the lookup value is not found:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
The IFERROR / VLOOKUP formula returns a blank cell instead of the error message.

If you'd rather display your own message instead of a standard Vlookup error, type it between the quotation marks, like this:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Oops, no match is found. Please try again!")
Display your message instead of VLOOKUP error.

Using VLOOKUP with ISERROR

Since the IFERROR function was introduced in Excel 2007 only, in lower Excel versions you will have to use the combination of IF and ISERROR functions in this way:

=IF(ISERROR(VLOOKUP formula), "Your message if any", VLOOKUP formula)

For example, here's the IF / ISERROR / VLOOKUP formula analogous to the IFERROR / VLOOKUP formula above:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)), "", VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

That's all for today. Hopefully, this short tutorial will help you cope with all possible VLOOKUP errors and get your formulas to work in the way you want.

You may also be interested in:

89 Responses to "Excel VLOOKUP not working - solutions for N/A, NAME and VALUE errors"

  1. Derek Robins says:

    We had a problem when we upgraded to 2013.
    When we sorted a table with a column containing the VLOOKUP formula
    =VLOOKUP(Analysis!D3,DEPT2,2,FALSE)
    The formula kept the reference to original cell that we were referring to
    So, if the above formula was located on row 3, and the sort moved it to row 41, it would still reference D3, rather than D41
    The table was located in the worksheet named "Analysis", so that worksheet reference was superfluous, and when we removed Analysis! from the formula, it adapted correctly to the new row following the sorting of the table.

  2. Jafar says:

    Thank you Svetlana

    you are really sweet girl. what you are presenting here of useful material reflects your sweetness, kind heart and sole.
    Thank you again love you and keep on.

    Cheers

    Jafar
    Saudi Arabia

  3. joody says:

    Dear,

    thank you for the great information , but I review all the possible problem and I fix them , but steel #N/A! and the Looked up value is exist!!

    so here is the lookup: =VLOOKUP(A2,C2:D1048534,1,FALSE)
    the column A have numbers which I need to make sure if it's exist or not then give me the value for it's reference. and C, D are the array index.
    and I used False because it's phone number I need the exact match.finally 1 will return the subscription date.

    they both have the same type , I check with =TYPE() , also they are match I checked with A2=D3.

    :) so how can I fix it?
    thx

  4. Ben Chanthoeun says:

    Thank you so much for your Manual uploaded

  5. Chris says:

    Please help!

    Disclaimer: I'm a newbiew with excel. I've created a table which has a couple hundred items which is referenced to by a vlookup. For some reason, when I am using the drop down to select an item, the drop down only shows 196 items from the table. Nothing more. The table array in the formula is correct and locked. BUt for some reason the dropdown list will only go so far. Help?

  6. Hi Chris,

    It's hard to determine the cause of the problem without seeing your data. Anyway, this is not because of Excel limitations. Even in older versions of Excel, a limit to the number of items displayed in drop-down lists was 1,000. In modern versions it's 10,000.

  7. Michael Heavener says:

    It's just not working. I used the wizard to build the formula =VLOOKUP('Tracking In Progess'!A:A,A:A,1,'Tracking In Progess'!A:C) and it always returns #VALUE!-- I tried swapping the lookup_value and table_array and it always returns #VALUE! I've tried doing this with five different workbooks with different data. It never works. I've tried it in 2010 and 2013 and the result is always the same. I need this function -- but I hate it!

  8. Oly says:

    I have been struggling for hours , you saved my life. I would have never guessed that the lookup column had to be the leftmost column of the table array..

  9. Marcus says:

    This is very helpful! Thank You. Unfortunately, my VLOOKUP is still not working and I'm receiving the #N/A. I know what the issue is, but can't fix it. I am looking in a range that doesn't contain the VLOOKUP value I would like (which is ok) and I just want it to return to zero (0). Is there any way I can do that in excel?

  10. Nadine says:

    found the answer:
    add Iferror to your formula:

    =IFERROR(VLOOKUP($R2;'my database'!$E:$U;6;FALSE);" ")

  11. Kevin Mcalister says:

    Hi Sveltana,

    My problem was a silly one my lookup search column wasn't sorted so it was returning a few N/A# but now it's solved. I owe you one and you got my gratitude it was really appreciated.

    Thank you very much for posting this is really useful.

  12. Stanley says:

    i what to compare my list of names, Numbers, dates in 2 sperate excel spreadsheet for each of them to finds erros.for example, i want boths spread sheet to have save figure 1245 and 1245. it if any contain 1254 then how can i corret it. example 2, i want stanley on both spreadsheet, it the ohter contain stanely, i can excel tell me the teh mistake and how can i fix it.

    can anyone help me please.

  13. Tony says:

    1- Can i use vlookup formula to search for data in an excel sheet that has blank rows or columns?

    2- can i build my vlookup formula on the right of the data that I need to search it?

    thanks,
    Tony

    • Debren says:

      Hi Tony,
      1) yes, the VLOOKUP formula can be used to search through blank rows/columns.
      2) yes, it's up to You where You place your formula.

  14. Dane says:

    I have an Excel 2010 document that has vlookups and other formulas. I want to share the Excel doc so that my team can update their parts and we can all be in the file. The only problem I am having is that when the document is being shared and I go to update it with new data (I am the only one on the document when I add new data) for everyone to update the vlookups stop working. What can be causing this issue?

  15. Anonymous says:

    THANK YOU!

  16. tpm says:

    The problem I frequenly run in to is the data that is being displayed by the Vlook up is both correct and incorrect. It pulls data from the wrong cell. Im pritty sure I set everything up correctly but why would it present incorrect data why not just N/A or error? In addition some of the data is represented properly. Seems sporadic on one sheet driving me nuts.

  17. Summer says:

    I have a spreadsheet that we update daily on one tab and we use a vlookup to update information on the main tab. The problem is that there are several client names that won't update automatically after we paste the new data. We have to clear their name each day and retype it for the vlookup to work. I have ran trim and that doesn't work. Plus my colleague cannot even update the name. The vlookup won't work at all if she updates from her excel. The names are in alphabetical order. The range is covered. THe names aren't all at the end they really have nothing in common that I can see. It seems completely random. If I retype their names the vlookup does not return the #NA but if my colleague retypes their names her Excel does not update the data. I have gone crazy trying to figure this out. Help!

  18. Eric says:

    Brilliant, Thanks a lot, I've searched high and low for an answer and this is the only one that I can understand and make work.

  19. Anita says:

    I have been using a the same Lookup in a large spreadsheet for about 4 years now. Recently I have been getting random results. In some of the cells I get the expected results but in others I just get a 0, not N/A or #Value#. I've made sure that my table is sorted in the correct order and that the data I'm looking for is in the furthest left column. I've also made sure that I have the correct range in my formula, nothing seems to help.

    What could be causing this random error in my results?

  20. Nitesh says:

    I am using excel 2013. when I try to do vlookup,the first row prints correct value. when I drag the formula for other columns it prints the same first value for all the other columns even if the formula is changed.

  21. Lane says:

    Hi there, I would appreciate your help.
    I created a table of VLookup formulas. Worked great, but now I am trying to sum values in several rows and I get a Zero.
    I would also like to average, counta and several other functions but it doesn't seem to work on the values created by VLookup.
    What am I doing wrong.
    Thank you so much for your help.

  22. Ray Pastor says:

    VLOOLUP not giveing correct number.
    Never mind. I found the problem. There were duplicate values and it grabbed the largerst value

  23. Vinod says:

    How can I search the numbers with E+ in my databases of thousands records

  24. Vinod says:

    How can I search the numbers with E+ in my databases of thousands records.

  25. Lala says:

    Every time I enter a vlookup lately (I didn't use to have this problem), if returns something like =VLOOKUP(A:A,'[Restricted Detailed Employee Listing_4.14.2015_limited.xlsx]Detailed Employee Listing'!$A:$S,false,19). Please help.

  26. Cris V. says:

    hi,

    I have an assignment, using vlookup and if functions,
    we cannot use the "if error" to not shown the "n/a",
    what function I can use instead?

    Thank you

    Cris

  27. Sheela says:

    hi,

    when using the following formula =VLOOKUP(A:B,Sheet2!A:C,3,2) getting value error. pls help.

  28. Alison Twibell says:

    I am trying to do Vlook up and it keeps on giving me #n/a
    It is reading from 2 worksheets within one document;
    vlookup($A3,'Venue Options'!$A:L,2,FALSE)
    I have checked that my columns are in ascending order and that the cell format is set at number....
    I am at a loss! I know it is difficult to see what I am doing but this is a relatively easy formula. ..
    Thank you
    Ali

  29. Aditya says:

    I have a list of members per month for three months and have to combine all the members of those months and mark in front of the members who were present in that perticular month. I know that it can be done by vlookup but i am not able to do it.

    Please help.

  30. Josh says:

    I was wondering if you could help me with an issue I have been having. I pull daily spreadsheets with information for employees. I have a column at the end that has my notes for each individual case. With each spreadsheet I use vlookup to transfer the most current notes from the previous day’s spreadsheet to the one I just pulled. The lookup value I use is the employee’s ID number which I always remember to convert to number. Every time I transfer the notes, the majority are correct but quite a few either populate as ‘0’, ‘#N/A’, or pull previous notes from much earlier spreadsheets. I’ve been on numerous excel forums looking for a solution and have tried many(locking the reference table array, sorting and filtering each spreadsheet the same way, etc) and none seem to work. I really appreciate any insight you can offer, correcting each spreadsheet has been a timely process that I feel I can be better spending doing other activities.

  31. Lana H says:

    Hello,

    Excellent post! Out of all the pages I looked at to resolve this problem, yours worked. So simple and Boom!!

  32. Ramesh Shukla says:

    may anyone help me..? during using VLOOKUP i am facing problem, actually i have a data which have extra spaces and "" quotes etc in table array but showing simple as lookup value and obviously i am unable to get the data so pls help me..

  33. John says:

    SL No Part # Description SL No Part # Description
    1 1100316 PLATE SPRING UPPE 1 1100316 #N/A
    2 1100611 KEY WOODRUFF CLUTCH 2 1100342 #N/A
    3 1100342 BEARING BALL 3 1100611 #N/A
    4 1101008 ROLLER 4 1100818 #N/A
    5 1101205 SPRING RETURN 5 1101008 #N/A
    6 1180123 BALL STEEL 6 1101205 #N/A
    7 1100818 WASHER BRASS 7 1180123 #N/A
    __________________________________________________________________
    I have checked all the columns if the

  34. Mark W says:

    I am using a vlookup to match sales and on hand data for item numbers that contain an'*' in them. The lookup is working except for occasion where the exact value is not found, but something close is found. I am told that the wildcard '*' is the reason the formula brings back a bad result even when the vlookup uses the FALSE condition (=vlookup(A1,G1:H13000,2,FALSE. The target looks like B110015BS***GG but pulls from B110015BSR**GG. Does anyone know how to make the formula ignore the wildcard? I tried doing a find/replace, but as a wildcard, it replaced the whole string in each field..

  35. Martin Alonso says:

    Hello, this post helps to reduce time on vlookup formula, thanks

  36. stg says:

    If nothing else works for number search (i.e. excessive blanks removed, data formats made the same number format) this works for me:

    =vlookup(A1/1,D1:D100,2,FALSE)

  37. josselle says:

    Bank Code Legend:
    bpi BPI

    BEGINNING BALANCE 258,581.64
    ADD:
    CASH DEPOSIT 270699
    A/R - COLLECTIONS 530,400.00
    FUND TRANSFER IN 0.00
    TOTALS 801,099.00
    DEDUCT:
    CASH REMITTANCE 0.00
    FUND TRANSFER OUT 0.00
    EXPENSES 414,350.00
    TOTALS 414,350.00

    ENDING BALANCE TO DATE 645,330.64

  38. josselle says:

    Bank Code Legend:
    bpi BPI

    BEGINNING BALANCE 258,581.64
    ADD:
    CASH DEPOSIT 270699
    A/R - COLLECTIONS 530,400.00
    FUND TRANSFER IN 0.00
    TOTALS 801,099.00
    DEDUCT:
    CASH REMITTANCE 0.00
    FUND TRANSFER OUT 0.00
    EXPENSES 414,350.00
    TOTALS 414,350.00

    ENDING BALANCE TO DATE 645,330.64

    good day!
    i just wanna know how can i show/flash the names of clients (which is in the other sheet) who paid cash in my report? same with the client who paid their accounts receivable, which i termed "collection"?

    i just want to show my boss the names of clients who paid their obligations (cash payment & collection of A/R).

    i used vlookup to find the details about the "bank" (bpi) the beg balance, the cash deposit, collections etc. what i want to solve now is how can i make the names of the clients appear in my report to see in details who the clients are already based on the data i encoded. and if my boss tries to see the details of another bank, same thing i want to appear the names of clients i encoded, also..
    please help me..

    Thanks!!

  39. josselle says:

    your utmost consideration and accommodation regarding my request would be of great help...please...

    thanks

  40. Dee says:

    I keep having my vlookup column doing this. Is there something I have to do to correct this?

    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)

  41. @Naeem Khan says:

    Hi, svetlana cheusheva
    i have a query related to excel please help me
    how to highlight past dates?
    Hope you help me!

  42. Abdullah Eyles says:

    Thanks for your explanations. The problem with my vlookup was the formatting of the "search" data; formatting it as "number" solved the problem!

  43. Gautam Lapsiya says:

    Thanks Svetlana

  44. Jacoba says:

    Svetlana

    It seems you may be able to help me. I am using Excel 2010. I created a drop-down list, and want to use VLOOKUP to complete other columns, with information in the table which I used for the drop-down list. The column for the drop-down is A.

    If I make my selection from the drop-down list, nothing happens, that is my formula, e.g:=VLOOKUP($E2,Premises!$A$2:$P$101,4,FALSE) remains in the cell, with no value provided. I checked formatting of cells to make sure it is the same.

    Thanks
    Jacoba

  45. Mahir Mohammed says:

    In certain parts of my worksheet , when i enter the vlookup function it doesnt do anything it remians as text format

  46. brian says:

    Had a similar issue today with a vlookup. I tried the suggestions above but nothing worked. I had to insert a value() into the field lookup value in order to conduct the search. Oddly, the field being looked up is formatted properly as well as the range it is looking at. An example:

    =VLOOKUP(VALUE(A1691),DSI!$B$2:$D$769,3,FALSE)

  47. Susan says:

    Hi,
    My V-lookup is working, but the formula will not copying down the full column of data. For example, 100 cells of data, first cell will v-look up correctly, but the rest of the column can't be completed without repeating the v-lookup an additonal 99 times, in each cell. Does that make sense?
    Any thoughts on how to fix?
    Thank you.

  48. Sharon says:

    THANK YOU!!!! You just saved me. TRIM...who knew!

  49. barani says:

    I too facing the common error like #N/A, even after i convert the look up range in number format. kindly suggest. any other alter.

  50. rarmandi says:

    A problem with…
    "Use VLOOKUP and INDIRECT to dynamically pull data from different sheets"
    =VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)reference formula

    Since my reference cell Y3 needs to be a number and my Named Ranges can start with a number (LIST15, LIST18, LIST20) I cannot get this formula to work.

    =VLOOKUP($AE3,INDIRECT($Y3&"_LIST"),2,0)

    I even tried to move the underscore to the right side to match the way it is in the Named Ranges and still don’t work.

    =VLOOKUP($AE3,INDIRECT($Y3&"LIST_"),2,0)

    I get a #REF! Error in both cases. Can anyone help?

  51. Daniela Esparza says:

    Hello, I've been having some trouble using this formula.

    I've got this code that I have to divide in three parts which I've resolved using LEFT,MID and RIGHT functions. Each part has to be reidentified with some data, using VLOOKUP I have identified the first and last part (which were letters), and I tried using this function for the middle part (which are numbers) but it returns #N/A. I tried changing the cell format from text to numbers but it's the same result

    Code: q-15-PAL
    =MID(B2,3,2) = 15
    =VLOOKUP(E2,M2:N38,2) = Palanca (this is what's supposed to appear)

    can somebody help me?

  52. manoj chemutu says:

    Thanks !!

  53. tom says:

    Hi experts,

    is it possible to look for a value which is the formula? I tried vlookup but not working, when I put look up value manually then it`s fine.

    thanks for help!
    tom

  54. arivu says:

    Hello,

    I am facing problem during using of VLOOKUP formula , Row & column number not showing.. anyone can give your advice?

  55. harish says:

    nice tricks

  56. Sivakumar says:

    Hi,

    I have two excel sheets one contains insurance policies issued by the insurance company through insurance broker along with the premium amount and the commission amount and another excel sheets which is provided by the insurance broker which has policies issued on behalf of the insurance company it has premium amount and the commission amount. Now my question is, i want to match both excel sheets by using vlookup. I matched large volume of data. But, when the same policy number is appears twice or thrice, vlookup showing the policy which comes first and ignores the remaining two. I want to know about the policy which are appearing twice or thrice with relevant premium amount. I read the how to get all duplicate values in the vlookup range. However, it seems like it is small volume of data. How do i it for the large volume of data?

  57. Nina says:

    Thank you so much, Svetlana, for the troubleshooting tips! Without them, I was struggling for hours trying to get VLOOKUP to work across two excel workbooks. What finally worked for me were sorting the lookup column in ascending order, and bringing that column to lie AFTER the starting column index of the Lookup table (as you suggested above). Many thanks, especially as I did not find such trouble shooting steps in Excel also.

  58. Ravi says:

    Hi,

    I have two worksheets, one with the list of names with related information in each row, and the other is for the query. I provide the name in one of the cells of my query sheet, and I want to use the MATCH function to get the row number (from the names worksheet) of the specified name.

    I am trying to use the following:
    =MATCH($B$3,$B$2!A1:A10, 0)

    Thanks in advance.

    -Ravi
    Where I specify the required name to be queried in the B3 cell of the query sheet, and B2 cell has the name of the names sheet.
    In short, I want to use variables to specify the lookup-value and lookup-range. How do I go?

  59. shamalmadingdong II says:

    very gud I enjoy

  60. Edward M says:

    Hello:
    We have an unusual problem with VLOOKUP. First, the format we use:

    =VLOOKUP(A6,dec2016register,7,FALSE)

    This command is in the 2017 workbook and is retrieving a number from another separate 2016 workbook.

    Where dec2016register is the named array for a worksheet in one workbook and has a value we want to export to a worksheet within another, separate workbook. Both workbooks are open, both reside in the same folder. We defined the array, there is no data conflict with the Lookup Value in either worksheet, and the exact same command (with different arrays) works perfectly in one other application involving two separate workbooks. BUT with this one particular application, we keep getting a #NAME? error. Why? What are we doing wrong???

  61. SV says:

    Hi,

    I am trying to create a vlookup and it is not getting executed. The below formula is shown in the column instead of the result

    =VLOOKUP(A3,'[11i Extract 300000.xlsx]Sheet1'!$A$1:$A$3995,1,false)

  62. Dennis A says:

    This is driving me nuts. The VLOOKUP worked on my 2015 data. but when I replicated it with 2016, using the same formulas, the lookup fails retunring #N/A...

    Col B is a list of names, such as
    Name
    Jaclyn Smith - Sr
    Yvette Burcescu - Jr
    Mychael Vernon - Fr
    Kiara Adams - Jr

    the lookup array is
    Name_(11) Name Section Award
    Aida Rossi Aida Rossi - Valhalla All Conference Honorable Mention
    Alexandra A Alexandra Antony - Blind Brook All Conference Honorable Mention
    Amanda Marj Amanda Marji - Yonkers All Conference Honorable Mention
    Anjali Seba Anjali Sebastian - Clarkstown North All Section

    Formula is =IFERROR(VLOOKUP(LEFT(B42,11),Names_Awards_2016,3,FALSE),"") returns an #N/A error, however, if I replace the LEFT(B42,11) with the actual texts string ex:"Jaclyn Smit", the formula appears to work.

    Help.

  63. veera says:

    while using vlookup for row and column detail not showing... pls help

  64. AMS says:

    Hi
    how can I use some Vlookups in a cell;means I want to Vlookup some tables and show one of answer(or even first on)in a cell?
    The symbol &(between Vlookups) didn't work here!:-(

    thanks for Help
    AMS

  65. Premjee Rajput says:

    I have a tale on excel file that contain vlookup function when i open that excel file on other network computer its convert to range what is the problam with that anyone please help me

  66. Nehal says:

    Hi

    How can i change format of 112-65-4171
    to
    812-48-7524

  67. Rob says:

    I have been struggling with the following issue and all the recommended fixes have not worked.
    I am using vlookup to pull data from four other sheets. I need to be able to use the results in cell to trigger the next search, however what happens is that the next vlookup looks up the values in the formuala, not the result.
    I.E.
    =VLOOKUP(E3, DATA!A2:Z480, 4, FALSE)

    and in E3 I have:

    =VLOOKUP(A2, OTHER! A1:Z400, 2, FALSE)

    Instead of looking for the displayed value in E3, it goes chasing down the previous VLOOKUP and tries to find A2, and if THAT was another formula, it just keeps going.

    I have changed all cell formats to general, number or anything but text and have turned off 'display formulas'.

    Anyone got any suggestions?

    Thank you.

  68. anu says:

    I need some VLOOKUP formula help

  69. Khalid says:

    I have pay grade coded as 4-X1, 4-X2 representing salary 50,000 and 65,000 in two columns.
    I get #N/A error using vlookup function when looking for any pay grade.. for example 4-X1

    Why do I get this error? please assist!!

  70. Lucas Gouvea Silva says:

    Very nice post. Solve my problem.

  71. Faraz Junaid says:

    Hi All,

    There is a problem when I try to compare two excel sheets using vlookup and if both the columns are empty which are compared. It returns an error. what to do in this condition???
    Best Regards,
    Faraz

  72. BALAJI says:

    AMAZING WORK THANK YOU VERY MUCH

  73. Rakesh says:

    I have 2 sheets with below data and i want to copy data(value) from sheet 2 to sheet1

    sheet1:
    id value
    1
    3
    2

    sheet2:
    1 A
    2 BB
    3 C

    vlookup used: =VLOOKUP(Sheet2!A2,Sheet2!A2:B4,2,0)

    Result:
    1 A
    3 BB
    2 C

    Expected:
    1 A
    3 C
    2 BB

    I am looking for exact match for the value being searched. However, it is giving me values as per sequence.

    Any help would be appreciated.

  74. Justin says:

    Convert to number, convert to number, convert to number..... I always forget to do that :P

  75. Anna says:

    Hello!
    I am doing match. If we put =iferror...;" ") we get blank cells instead of #N/A. That is ok, BUT in that case I want to keep my original number. Is it possible?
    Thank you for the answer!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard