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.

 

[wrightyrx7]

Im pretty sure that any of the lookups after the first are irrelevant. If the first one error's so will all the rest.

What im assuming your trying to do is.... if column 2 returns nothing the check column 4, if column 4 returns nothing then check column 6 etc??

If not i think we need more of an explanation.

 

[AlanY]

try change the option to 0 for all the vlookup

IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,2,[COLOR="#FF0000"]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,4,[COLOR="#FF0000"]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,6,[COLOR="#FF0000"]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,8,[COLOR="#FF0000"]0[/COLOR]),"Not Found"))))

 

[questforexcel]

Hi,

Thats what I am trying to do.

If not in Column 2, find in Column 4, if not in Column 6, then find in Column 8, if nothing than return 0.

Could you please help me with this.

Thank you

Im pretty sure that any of the lookups after the first are irrelevant. If the first one error's so will all the rest.

What im assuming your trying to do is.... if column 2 returns nothing the check column 4, if column 4 returns nothing then check column 6 etc??

If not i think we need more of an explanation.

 

[questforexcel]

I want my vlookup to pick values from a mixed pool of data.

Some are individual numbers eg. 102 , while some are ranges eg. 90-105, 106-120 etc

I would like my formula to show where my individual value falls within any of these ranges

try change the option to 0 for all the vlookup

IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,2,[COLOR=#FF0000]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,4,[COLOR=#FF0000]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,6,[COLOR=#FF0000]0[/COLOR]),IFERROR(VLOOKUP(LEFT(A4007,3),'USPS - Zone & Pricing Chart'!$B$4:$I$34,8,[COLOR=#FF0000]0[/COLOR]),"Not Found"))))

 

[AlanY]

could you post a small sample of the data here

 

[questforexcel]

Sure, below is a sample of the data.

I would like each individual cell to mentioned in "Postal Code" below to be drawn from the list of ranges mentioned in "Zip Code" "Zone" below.


[TABLE="width: 89"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Postal Code[/TD]
[/TR]
[TR]
[TD="align: right"]2575[/TD]
[/TR]
[TR]
[TD="align: right"]7632[/TD]
[/TR]
[TR]
[TD="align: right"]9314[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 199"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ZIP Code[/TD]
[TD]Zone[/TD]
[TD]ZIP Code[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD]005-076[/TD]
[TD]6[/TD]
[TD]349[/TD]
[TD]1*[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]5[/TD]
[TD]350---352[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]078---079[/TD]
[TD]6[/TD]
[TD]354---368[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]080---087[/TD]
[TD]5[/TD]
[TD]369---394[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]088---089[/TD]
[TD]6[/TD]
[TD]395[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]090---098[/TD]
[TD]6+[/TD]
[TD]396---397[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]100---149[/TD]
[TD]6[/TD]
[TD]398[/TD]
[TD]4*[/TD]
[/TR]
[TR]
[TD]150---166[/TD]
[TD]5[/TD]
[TD]399[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]6[/TD]
[TD]400---418[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]168---212[/TD]
[TD]5[/TD]
[TD]420---427[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]214---268[/TD]
[TD]5[/TD]
[TD]430---433[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]270---279[/TD]
[TD]5[/TD]
[TD]434---436[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]280---285[/TD]
[TD]4[/TD]
[TD]437---462[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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

could you post a small sample of the data here

 

[AlanY]

so, what do you expect to get, e.g. Postal Code 2575?

 

[questforexcel]

So based on the first 3 digits of the postal code, i would like it to display which zone does it fall under.

So 257 should fall under Zone 5, which is correct.

The problem is with the 2nd postal code, 7632. It should fall under Zone 6, but my formula displays it as Zone 2.

Is there a better way that I could reference my data to pull out from different columns at once.

Thank you

[TABLE="width: 199"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ZIP Code[/TD]
[TD]Zone[/TD]
[TD]ZIP Code[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD]005-076[/TD]
[TD]6[/TD]
[TD]349[/TD]
[TD]1*[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]5[/TD]
[TD]350---352[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]078---079[/TD]
[TD]6[/TD]
[TD]354---368[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]080---087[/TD]
[TD]5[/TD]
[TD]369---394[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]088---089[/TD]
[TD]6[/TD]
[TD]395[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]090---098[/TD]
[TD]6+[/TD]
[TD]396---397[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]100---149[/TD]
[TD]6[/TD]
[TD]398[/TD]
[TD]4*[/TD]
[/TR]
[TR]
[TD]150---166[/TD]
[TD]5[/TD]
[TD]399[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]6[/TD]
[TD]400---418[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]168---212[/TD]
[TD]5[/TD]
[TD]420---427[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]214---268[/TD]
[TD]5[/TD]
[TD]430---433[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]270---279[/TD]
[TD]5[/TD]
[TD]434---436[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]280---285[/TD]
[TD]4[/TD]
[TD]437---462[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]5[/TD]
[TD]463---468[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]287---306[/TD]
[TD]4[/TD]
[TD]469---479[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]307[/TD]
[TD]5[/TD]
[TD]480---509[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]308---312[/TD]
[TD]4[/TD]
[TD]510---513[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]313---315[/TD]
[TD]3*[/TD]
[TD]514---516[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]316---317[/TD]
[TD]4*[/TD]
[TD]520---528[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]318---319[/TD]
[TD]4[/TD]
[TD]530---532[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]320[/TD]
[TD]3*[/TD]
[TD]534---535[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]2*[/TD]
[TD]537---539[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]322[/TD]
[TD]3*[/TD]
[TD]540[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]323---325[/TD]
[TD]4*[/TD]
[TD]541---545[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]326[/TD]
[TD]3*[/TD]
[TD]546[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]327---333[/TD]
[TD]2*[/TD]
[TD]547---549[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]1*[/TD]
[TD]550---551[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]335---342[/TD]
[TD]2*[/TD]
[TD]553---567[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]344[/TD]
[TD]3*[/TD]
[TD]570---577[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]346---347[/TD]
[TD]2*[/TD]
[TD]580---588[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

so, what do you expect to get, e.g. Postal Code 2575?

 

[GR00007]

Hi,
If not in Column 2, find in Column 4, if not in Column 6, then find in Column 8, if nothing than return 0.

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'