Excel VLOOKUP function returns a value from the wrong row – but no trailing spaces

microsoft excelvlookupworksheet-function

I'm having a problem with the VLOOKUP formula and although other people online have had issues, their problems seem to relate to trailing spaces, however I've checked my data and can't find any trailing spaces so I'm still unsure what could be causing this.

The formula I'm using is: =VLOOKUP(G1, A:B, 2)

Cell G1 value = "Shipping – Standard Delivery by 5pm"

<excel
The array A:B is as below:
Column A                               Column B
Shipping Rate Name                     Service
2 Day (Isle of Man)                     11
2 Day (Scilly Isles)                    11
2 Day (Scottish Highlands)              11
Saturday by 10am                        18
Saturday by 12pm                        17
Saturday by 5pm                         16
Standard Delivery (Channel Islands)     12
Standard Delivery (EIRE)                12
Standard Delivery (Northern Ireland)    12
Standard Delivery by 10am               14
Standard Delivery by 12pm               13
Standard Delivery by 5pm                12
excel>

ignore the "< excel >" marks they are there just for formatting to make the columns clearer!

The issue I've got is the result of the formula is "16" whereas it should theoretically be "12" (that's what I hoped for anyway)

If anyone can help it would be very much appreciated!

EDIT: some more notes:

  • IF I put FALSE(Exact Match) at the end of the formula, the result is "#N/A". If I put TRUE (Approximate Match) at the end, the result is still "16"

  • I didn't note that each value is actually on a different sheet, I've just simplified it for this question. The Cell G1 is one sheet, the Array A:B is on the next sheet, and the formula itself is on another sheet.

  • It doesn't make any difference if I put all the array, formula and value all on the same sheet – I still get "16"!!!!

Best Answer

The problem you have is there is no match.

The formula is looking for the value in G1, "Shipping - Standard Delivery by 5pm", but there is nothing that matches it in the data array.

If G1 did not have the preceding text of "*Shipping - *", it would return the value you are looking for.

If I understood you correctly, you said the system the data comes from adds that "*Shipping - *" text when importing. So, adjust your VLOOKUP formula to include the MID function to trim off the unneeded text - MID(G1, 12, 99).

Withing the VLOOKUP it will look like this;

=VLOOKUP(MID(G1, 12, 99), A:B, 2, FALSE)

Note: I used 99 in the MID function because the length of the string is unknown. Excel ignores blank spaces at the end so this is not an issue.

Related Question