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 theMID
function to trim off the unneeded text -MID(G1, 12, 99)
.Withing the
VLOOKUP
it will look like this;Note: I used
99
in theMID
function because the length of the string is unknown. Excel ignores blank spaces at the end so this is not an issue.