Incorrect value picked up in Vlookup across multiple columns in one formula

[questforexcel]

Hi All,

I was trying to vlookup my data from a table with 4 columns, when I applied the following formula there were errors in a few of the calculations.

IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,2,1),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,4,1),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,6,1),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,8,1),"Not Found"))))

However, when I place all data in one single column, it picks the right values and figures.

Could you please help me to solve this.


I would like it to pick the values from the 4 column table and pick the right ones.

 

[questforexcel]

Thank you, this worked

However, just curious, is there no other way, to make excel lookup across different columns through one formula?

Would I always have to manually shift data into 2 columns.

Thank you

My suggestion is to modify your "USPS - Zone & Pricing Chart" to have two columns instead of 8. Take the data of columns C&D and put under A&B, likewise with E&F and G&H

=INDEX([COLOR=#574123][I]'USPS - Zone & Pricing Chart'![/I][/COLOR]$B$2:$B$181,MATCH(LEFT(A4007,3),[COLOR=#574123][I]'USPS - Zone & Pricing Chart'![/I][/COLOR]$A$2:$A$181,1))

Note: change 181 to be the last row of your concatenated columns of 'USPS - Zone & Pricing Chart'

 

[AlanY]

try this that don't need to rearrange your data

Book1
	A	B	C	D	E	F	G	H
1	Postal Code
2	2575	7
3	7632	8
4	9314	9
5
6
7	ZIP Code	Zone	ZIP Code	Zone	ZIP Code	Zone	ZIP Code	Zone
8	005-076	6	314	1*	314	1*	314	1*
9	77	5	350---352	4	350---352	4	350---352	4
10	078---079	6	354---368	4	354---368	4	354---368	4
11	080---087	5	369---394	5	760---765	8	369---394	5
12	088---089	6	395	4	395	4	395	4
13	090---098	6+	396---397	5	396---397	5	396---397	5
14	100---149	6	398	4*	398	4*	398	4*
15	150---166	5	399	4	399	4	931	9
16	167	6	400---418	5	400---418	5	400---418	5
17	168---212	5	420---427	5	420---427	5	420---427	5
18	214---268	7	430---433	5	420---428	5	430---433	5
19	270---279	5	434---436	6	434---436	6	434---436	6
20	280---285	4	437---462	5	437---462	5	437---462	5
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(LEFT($A2,3)>=LEFT($A$8:$A$20,3)),--(LEFT($A2,3)<=RIGHT($A$8:$A$20,3)),$B$8:$B$20) +SUMPRODUCT(--(LEFT($A2,3)>=LEFT($C$8:$C$20,3)),--(LEFT($A2,3)<=RIGHT($C$8:$C$20,3)),$D$8:$D$20) +SUMPRODUCT(--(LEFT($A2,3)>=LEFT($E$8:$E$20,3)),--(LEFT($A2,3)<=RIGHT($E$8:$E$20,3)),$F$8:$F$20) +SUMPRODUCT(--(LEFT($A2,3)>=LEFT($G$8:$G$20,3)),--(LEFT($A2,3)<=RIGHT($G$8:$G$20,3)),$H$8:$H$20)

 

[GR00007]

Very nice! Caution: there's one value that you want to make text: A9 = 77 should be '077, and I assume that your zip lookup will extend to the west coast (900's) so the $20 would become whatever the last row is for the 8 columns.

 

[GR00007]

I played around with that formula and found that if the zone column had a non-numeric (i.e. '2*' or '9+') it returned zero. I also found a zone chart from the post office (https://postcalc.usps.com/domesticzonechart). Please modify the lookup ranges if necessary, the fourth column set was two rows less than the other three.

Here's a revised format using separate tab for the lookup chart:

=SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$A$2:$A$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$A$2:$A$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$B$2:$B$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$C$2:$C$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$C$2:$C$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$D$2:$D$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$E$2:$E$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$E$2:$E$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$F$2:$F$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$G$2:$G$34,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$G$2:$G$34,3)),--LEFTI'USPS - Zone & Pricing Chart'!$H$2:$H$34,1))

 

[questforexcel]

Thank you so much for posting this. This formed the basis for working this.

 

[questforexcel]

It was very observant of you to point that out.

I had worked on all the data but was still getting #NA on my file, when i filtered it was all the "77" orders.

I manually put that out to '077 and it worked Thanks

Very nice! Caution: there's one value that you want to make text: A9 = 77 should be '077, and I assume that your zip lookup will extend to the west coast (900's) so the $20 would become whatever the last row is for the 8 columns.

 

[questforexcel]

Thank you so much for this, the left only 1 char for Zone column was helpful.

Alan Y's formula and your improvisation really saved my day.

If its okay with you, could you please help me understand the following:

1) I'd been reluctant to use the sumproduct formula cause of lack of knowledge on it.

Does sum product combine only 2 sets of data i.e. Left 3 characters of the zone and right 3 characters of the zone against one reference point i.e. the zone value. If I had more dependent factors, could it have even factored those to match against one reference point of the zone value?

2) This is relatively a simple question, what does the "--" before the left sign signify.

I played around with that formula and found that if the zone column had a non-numeric (i.e. '2*' or '9+') it returned zero. I also found a zone chart from the post office (https://postcalc.usps.com/domesticzonechart). Please modify the lookup ranges if necessary, the fourth column set was two rows less than the other three.

Here's a revised format using separate tab for the lookup chart:

=SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$A$2:$A$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$A$2:$A$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$B$2:$B$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$C$2:$C$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$C$2:$C$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$D$2:$D$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$E$2:$E$36,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$E$2:$E$36,3)),--LEFT('USPS - Zone & Pricing Chart'!$F$2:$F$36,1))
+SUMPRODUCT(--(LEFT(A2,3)>=LEFT('USPS - Zone & Pricing Chart'!$G$2:$G$34,3)),--(LEFT(A2,3)<=RIGHT('USPS - Zone & Pricing Chart'!$G$2:$G$34,3)),--LEFTI'USPS - Zone & Pricing Chart'!$H$2:$H$34,1))

 

[AlanY]

Thank you so much for this, the left only 1 char for Zone column was helpful.

Alan Y's formula and your improvisation really saved my day.

If its okay with you, could you please help me understand the following:

1) I'd been reluctant to use the sumproduct formula cause of lack of knowledge on it.

Does sum product combine only 2 sets of data i.e. Left 3 characters of the zone and right 3 characters of the zone against one reference point i.e. the zone value. If I had more dependent factors, could it have even factored those to match against one reference point of the zone value?

2) This is relatively a simple question, what does the "--" before the left sign signify.

1) sumproduct() function can deal with multiple sets of data, it just multiply them up similar to the AND function. to get the OR effect it need to add the individual sumproduct results like the one in post #12

2) the -- in front of the function is to turn the array(true, true, false, etc) to (1,1,0, etc) so that excel can add them up

 

[GR00007]

the -- in front of the function is to turn the array(true, true, false, etc) to (1,1,0, etc) so that excel can add them up

This is the real genius part (thanks to AlanY) - setting to a binary array (0's & 1's) would only return a value when the first three digits of the zip are between the values in the lookup and BOTH were 1 (true) being 1*1, every other situation would be 1*0.
The "--" in front of the Left(zone,1) turned it from text to numeric so it could be multiplied [how that worked I'm not really sure].