Share via

VLOOKUP Returns Wrong Value

Anonymous
2024-04-30T02:54:34+00:00

The VLOOKUP formula returns wrong value. Kindly see the attached screenshot for reference.

Excel 1:

Excel 2 with VLOOKUP formula:

Can someone help on how can I fix this? I already traced the data being returned per parameter, but still it displays the wrong value.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-04-30T20:43:05+00:00

    Glad you got your formula to work. You commented to me and to Snow Lu that you didn't understand why it was working the way it did without the last parameter. Here is a hopefully simple explanation.

    That last element determines whether Excel will look for an exact match or the closest match that must be in an alphabetized list. So, when the formula is set to 1 (or TRUE) Excel will not go down the list any further than the first occurrence of the letter that comes after the first letter of the lookup value. When it finds that letter it will choose the item right before that letter as the closest match. If things are not in alphabetical order, then Excel may stop looking for the Lookup Value well before it gets down to the actual match.

    I mentioned putting FALSE at the end in that place. Snow Lu put a 0 in that place. For this formula FALSE and 0 mean the same which is to look for an exact match and the data does not need to be alphabetized. TRUE and 1 mean the same that it will look for the closest match and MUST be alphabetized.

    TRUE or 1 is the default if the element is left out which is why your formula was defaulting to the closest match as Excel interpreted it and producing the wrong outcome.

    Here is an example. With a 1 (or TRUE) the formula is looking up Red in the table in A and B but getting the wrong result because, since it is set to look for the closest match in an alphabetized list, Excel stops when it gets to the Y in Yellow because it is after the R in Red in the alphabet and chooses the entry right before it.

    Image

    When the Range Lookup element is changed to 0 (or FALSE) then the formula will find the correct result even though it is not alphabetized since it is now only looking for an exact match.

    Image

    In addition, if Excel doesn't find an exact match in this case it will show that it can't with #N/A.

    10 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2024-04-30T03:30:14+00:00

    =vlookup(A2,'[Historical Sales Report 04-29-2024 (After Import).xlsx]Sheet1'!$A$1:$K$2910,7,0)

    4 people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-30T03:45:09+00:00

    Hi Snow Lu MSFT,

    I tried this and it worked. But I don't understand why it displays the wrong value if I don't put the 4th parameter. The lookup value I'm looking from Excel 2 into Excel 1 has no duplicate, it only have 1 match.
    Image

    But still thank you so much for helping me out. This is a big help.

    0 comments
  2. Anonymous
    2024-04-30T03:33:11+00:00

    Hi Rich~M,

    Thank you for the response.

    I apologize for the confusion. The data I'm trying to retrieve from Excel 1 is under column G.

    In the Excel 2, I already tried to change the third parameter to "6". It returns the value from column F.
    Image

    I tried to change it to "8" as well. It returns 0 instead.
    Image

    I'm not sure why it return values that way.

    0 comments
  3. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-04-30T03:09:20+00:00

    Hi Joseph. I am an Excel user like you.

    From what I can see in your screenshots, The data you highlighted in yellow that you want to be retrieved by the VLOOKUP function is in Column F. Column F would be the 6th column in your table, not the 7th. The 7 at the end of the formula should be a 6 if the desired result is in Column F as it appears. The formula is set to retrieve the data from Column G which is the 7th column in the table.

    Also, you left off an important element of the VLOOKUP function: the Range Lookup. After the 6 (or 7) you should have a comma followed by FALSE to identify that you want to find an exact match. See the syntax in red below. Without it the data has to be in alphabetical/numerical order in order to find the correct match, and even then, if there is not an exact match it will return the closest match which will likely be incorrect.

    VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

    0 comments