# Problem with value for VLOOKUP.



## BillP (Dec 20, 2022)

Good afternoon!

I'm trying to make a worksheet as simple as possible for another user.

I'm using VLOOKUP to get a county FIPS code based on a Zip Code. Normally, that's fine. But the lists the person has to work with have 9-digit Zip Codes (xxxxx-xxxx), and I need to pull out only the first 5 digits.

If I use the LEFT function in a helper column, I can see the 5-digit result, but VLOOKUP can't, and it gives a #VALUE error. I can't use the LEFT function inside a VLOOKUP forrmula. I don't want them to have to copy-and-paste-special-text each time.

What can I do to get a lookup based on only the first 5 digits that are automatically extracted?

Thanks for your help.


----------



## Candyman8019 (Dec 20, 2022)

What version of Excel are you using?  If you have O365, you can use the xlookup.

Book1.xlsmJKLMNO16ziphelperfips2045055551790010-2159001044441820450-0122045055551929281-145292816666Sheet3Cell FormulasRangeFormulaO16O16=XLOOKUP(N16,K17:K19,L17:L19)K17:K19K17=NUMBERVALUE(LEFT(J17,5))


----------



## Fluff (Dec 20, 2022)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You can use left inside vlookup like 
	
	
	
	
	
	



```
=VLOOKUP(LEFT(A2,5)+0,Sheet1!a2:b100,2,0)
```


----------



## Dave Patton (Dec 20, 2022)

A few examples posted with the forum's tool named XL2BB would be useful.

T202212a.xlsmABCDEF1CodeCountry255555-4444CC12345A312345-1234AA34567B434567-2111BB55555C54aCell FormulasRangeFormulaB2:B4B2=VLOOKUP(--LEFT(A2,5),$E$2:$F$5,2,0)C2:C4C2=XLOOKUP(--LEFT(A2,5),$E$2:$E$4,$F$2:$F$4,,0)

T202212a.xlsmABCDEF6CodeCountry755555-444455555CC12345A812345-123412345AA34567B934567-211134567BB55555C104aCell FormulasRangeFormulaB7:B9B7=--LEFT(A7,5)C7:C9C7=VLOOKUP(B7,$E$2:$F$5,2,0)D7:D9D7=XLOOKUP(B7,$E$2:$E$4,$F$2:$F$4,,0)


----------



## BillP (Dec 20, 2022)

Thank you!
I'm sorry I forgot to mention that I'm using Excel 365. I haven't been here in years.
I like the one-step solution from Fluff (I didn't know about the +0; I had everything else right).
But I also liked Cancyman8019's suggestion. I've never used NUMBERVALUE.
Nor have I used Dave Patton's -- .

You learn something every day, if you're not careful.


----------



## Dave Patton (Dec 20, 2022)

The +0  or  double negative --  coerce the text information to a number.  You can use either.


----------



## Fluff (Dec 21, 2022)

Glad we could help & thanks for the feedback.


----------

