none
A bug of Vlookup or my mistake?

    Question

  • I'm using the Vlookup function in MS Office Excel 2007 and my formula is like this: =VLOOKUP(K146,C145:F252,4), where K146 is a string (ie in my case a university name I would like to find in a batch of data) and C145:F252 contains that pool of data, in which C column contains the name of university, D & E columns are some statistical figures and F is the country of the relevant university of the same row. The result is stored in K149 (ie the cell K149 is =vlookup(......).
    However, when I type the above formula next to the pool of data on the right with several columns in between, it just shows #N/A. I've tried giving the K146 quotation marks (ie"K146"), it does show me my expected result. However, I have so many entries that I can't possibly type the quotation marks for all of them. Also, I've tried limiting the second position (ie searching area) of the formula to only the row where the relevant information I want to look for is, it also works. So, is it a bug of the MS Excel? If so, is there any other function which can do the trick? Thank you very much for reading and trying to answer my question!
    Tuesday, March 20, 2012 8:49 AM

Answers

  • Hi Thomas,

    Yes, that is what I mean. In that case, you've already set things up properly and something else is messing things up. to test if your formula works, copy one of the cells in your lookup table and paste it in the cell which the VLOOKUP uses to do the lookup. It should find it then.
    If so, carefully look at the contents of the first column in your lookup table, there may be leading or trailing spaces there?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Wednesday, March 21, 2012 6:58 AM
  • Hi Thomas,

    There are two possible reasons for the error “#N/A”.

    The first one is : If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

    The second one is: When the Range_lookup parameter is set as false, and an exact match is not found, the error value #N/A is returned.

    As your formula, the Range_lookup parameter is omitted( this seems to be the same as True), so the second cause is not the exactly reason.

    So please check whether the value in cell K149 is in the range C145:C252. And if not, and the value is smaller than the smallest value in this range, the error will occur.

    More detail about VLookup function, please refer to the link:

    http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

    Jaynet Zhang

    TechNet Community Support

    Friday, March 23, 2012 3:47 PM

All replies

  • If you use K146 without the quotes, all Excel does is extract the value from cell K146 and use that to look up.
    So put your lookup codes in cells and point the VLOOKUP function to those cells.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, March 20, 2012 9:09 AM
  • So put your lookup codes in cells and point the VLOOKUP function to those cells.


    Do you mean I click the fx button and point it to the cell containing the words I want to look for? I'm already doing that but still the result is not what I want. Sorry I'm a lay person when it comes to excel. Could you explain a bit further just to make sure I follow you? Thank you!

    Wednesday, March 21, 2012 3:22 AM
  • Hi Thomas,

    Yes, that is what I mean. In that case, you've already set things up properly and something else is messing things up. to test if your formula works, copy one of the cells in your lookup table and paste it in the cell which the VLOOKUP uses to do the lookup. It should find it then.
    If so, carefully look at the contents of the first column in your lookup table, there may be leading or trailing spaces there?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Wednesday, March 21, 2012 6:58 AM
  • Hi Thomas,

    There are two possible reasons for the error “#N/A”.

    The first one is : If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

    The second one is: When the Range_lookup parameter is set as false, and an exact match is not found, the error value #N/A is returned.

    As your formula, the Range_lookup parameter is omitted( this seems to be the same as True), so the second cause is not the exactly reason.

    So please check whether the value in cell K149 is in the range C145:C252. And if not, and the value is smaller than the smallest value in this range, the error will occur.

    More detail about VLookup function, please refer to the link:

    http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

    Jaynet Zhang

    TechNet Community Support

    Friday, March 23, 2012 3:47 PM