vlookup not working as expected

[dmcgov]

vlookup error.png

So lets set the stage for this issue.

i have a number of NA's in this image. I need for them to fill out.

so my Project Name (D1) field is: "=VLOOKUP(C7,Formulas!J:K,2,FALSE)"

and my Project/Job (C7) field is: "=IF(LEFT(D10,4)="Proj","",LEFT(D10,4))"

and my lookup for the Project/Job (D10) field is found on the formulas tab, range L:L

if i manually type in the Project/Job (C7) like 1051, then D1 shows up as "2016 Handcraft Maintenance"

for the range D5:D9, typing in the vendor number (C4), will fill out the range, but not if i use the lookup method

Currently the formula in D5 is "=VLOOKUP($C$4,Formulas!$Q$1:$V$308,2,FALSE)"

Im uploading the spreadsheet, use PO xx1 tab to test.

[AliGW]

Try this in C4:

=VALUE(IF(LEFT(B10,4)="Vend","",LEFT(B10,4)))

[dmcgov]

i get a #Value! in C4, see image below:

vlookup error 2.png

[MrShorty]

The LEFT() function in your C7 formula "=IF(LEFT(D10,4)="Proj","",LEFT(D10,4))" returns a text string "1051". When you hand enter 1051, it will be entered as a number, not a text string. Excel sees the text string "1051" and the number 1051 as different. Your pictures do not show your lookup tables, but I would guess that, in your lookup tables, the lookup values are numbers and not text strings. When you search for the text string "1051" in a list of numbers, it is not found.

My first thought for a solution is to make sure you C7 formula returns a number and not a text string. I might simply nest the LEFT() function inside of a VALUE() function =IF(...,...,VALUE(LEFT(...))). That should force text "1051" to the number 1051, which will be found in your lookup table.

[AliGW]

That's because your B10 does not contain the correct data. To get rid of the error message, add IFERROR(...,"") round the formula (...).

[dmcgov]

The LEFT() function in your C7 formula "=IF(LEFT(D10,4)="Proj","",LEFT(D10,4))" returns a text string "1051". When you hand enter 1051, it will be entered as a number, not a text string. Excel sees the text string "1051" and the number 1051 as different. Your pictures do not show your lookup tables, but I would guess that, in your lookup tables, the lookup values are numbers and not text strings. When you search for the text string "1051" in a list of numbers, it is not found.

My first thought for a solution is to make sure you C7 formula returns a number and not a text string. I might simply nest the LEFT() function inside of a VALUE() function =IF(...,...,VALUE(LEFT(...))). That should force text "1051" to the number 1051, which will be found in your lookup table.

ding ding ding. we have a winner!

Thanks Mr Shorty for that fix. now everything is golden.

[AliGW]

Thanks for the rep - glad to have helped!