Problem with value for VLOOKUP.

[BillP]

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]

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

Book1.xlsm
	J	K	L	M	N	O
16	zip	helper	fips		20450	5555
17	90010-215	90010	4444
18	20450-012	20450	5555
19	29281-145	29281	6666
Sheet3

Cell Formulas
Range	Formula
O16	O16	=XLOOKUP(N16,K17:K19,L17:L19)
K17:K19	K17	=NUMBERVALUE(LEFT(J17,5))

 

[Fluff]

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]

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

T202212a.xlsm
	A	B	C	D	E	F
1					Code	Country
2	55555-4444	C	C		12345	A
3	12345-1234	A	A		34567	B
4	34567-2111	B	B		55555	C
5
4a

Cell Formulas
Range	Formula
B2:B4	B2	=VLOOKUP(--LEFT(A2,5),$E$2:$F$5,2,0)
C2:C4	C2	=XLOOKUP(--LEFT(A2,5),$E$2:$E$4,$F$2:$F$4,,0)

T202212a.xlsm
	A	B	C	D	E	F
6					Code	Country
7	55555-4444	55555	C	C	12345	A
8	12345-1234	12345	A	A	34567	B
9	34567-2111	34567	B	B	55555	C
10
4a

Cell Formulas
Range	Formula
B7:B9	B7	=--LEFT(A7,5)
C7:C9	C7	=VLOOKUP(B7,$E$2:$F$5,2,0)
D7:D9	D7	=XLOOKUP(B7,$E$2:$E$4,$F$2:$F$4,,0)

 

[BillP]

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]

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

 

[Fluff]

Glad we could help & thanks for the feedback.