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

Question

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"!!!!

Solution

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.

Comments

[Admin]

By omitting the Range Lookup i.e. the last parameter in VLOOKUP an approximate match is returned. More over there's no 'Shipping' prefix in your Column A which exists in G1. Do you want an approximate or Exact match?

[Admin]

Added some notes to make it clearer!

[Admin]

Are you expecting that VLOOKUP with approximate match shall do an inline search i.e. it will find ‘Standard Delivery by 5pm’ from the string ’Shipping - Standard Delivery by 5pm’? VLOOKUP text search is alphabetic & sequential in nature on sorted data. The last closest previous value to ‘Sh…’ is ‘Sa….’ And that’s the third occurrence of ‘Saturday…’ in your data. Replace ‘Saturday by 5pm’ with ‘Sii’ and it will return number 17 the previous row which is the last closest previous match, approximate.

[Admin]

No I don't expect VLOOKUP to approximately match text strings. Both of the "Shipping - Standard Delivery by 5pm" values are exported from the same system into Excel so the text strings should match exactly from the value to the array. Is the problem caused by Excel trying to do an approximate match on the list?

[5Diraptor]

DUH! I think I'll get back in my box, can't believe how I missed this - so obvious but I was getting caught up on all the formula and so on and totally missed this! Many thanks!!!