Vlookup help please

[mina91709]

I AM TRYING TO USE VLOOKuP FEATURE TO LOOKUP TWO DIFFERENT CELLS AND PULL OUT VALUE FROM TWO DIFFERENT TABLE. SORRY IF MY EXPLANATION IS NOT CLEAR BUT HERE IS WHAT I AM TRYING TO Do

LOOK UP TABLES:

[TABLE="width: 500"]
<tbody>[TR]
[TD]CITY[/TD]
[TD]TAX RATE[/TD]
[TD]COUNTY[/TD]
[TD]TAX RATE[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9%[/TD]
[TD]LA[/TD]
[TD]9.25%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9.5%[/TD]
[TD]SA[/TD]
[TD]8.5%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9%[/TD]
[TD]SB[/TD]
[TD]7.25%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]10%[/TD]
[TD]OC[/TD]
[TD]8.5%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]10.5&[/TD]
[TD]SL[/TD]
[TD]8%[/TD]
[/TR]
</tbody>[/TABLE]

CELLS TO MATCH AND MATCH INPUT FROM THE LOOK UP TABLES:
[TABLE="width: 500"]
<tbody>[TR]
[TD]CITY[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]COUNTY[/TD]
[TD]LA[/TD]
[/TR]
[TR]
[TD]TAX RATE[/TD]
[TD]RETURN VALUE SHOULD BE THE VALUE OF THE CITY WHICH IS %9 AND IGNORE THE COUNTY[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]CITY[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]COUNTY[/TD]
[TD]LA[/TD]
[/TR]
[TR]
[TD]TAX RATE[/TD]
[TD]RETURN VALUE SHOULD BE THE COUNTY BECAUSE THE CITY IS NOT LISTED. VALUE WILL BE %9.25[/TD]
[/TR]
</tbody>[/TABLE]


THANK YOU VERY MUCH

 

[dUBBINS]

Looks like this works:

=IF(ISERROR(VLOOKUP(F1,A1:B5,2,FALSE))=TRUE,VLOOKUP(F1,C1:D5,2,FALSE),VLOOKUP(F1,A1:B5,2,FALSE))

Alter it to your correct cells and ranges of course.

 

[liveinhope]

With your tax table in columns A to D "city to look for" in H1, and county to look for in H2

Find city tax rate formula =VLOOKUP($H$1,$A$2:$B$20,2,FALSE)
find county tax rate formula =VLOOKUP($H$2,$C$2:$D$20,2,FALSE) (always returns county rate, even if city match exists)

single formula =IFERROR(VLOOKUP($H$1,$A$2:$B$20,2,FALSE),VLOOKUP($H$2,$C$2:$D$20,2,FALSE)) looks for city level match .. if that fails look for county level match

all formulas based on city-county data in rows 2 to 20 . adjust if needed

[TABLE="width: 742"]
<colgroup><col span="4"><col span="3"><col><col></colgroup><tbody>[TR]
[TD]CITY[/TD]
[TD]TAX RATE[/TD]
[TD]COUNTY[/TD]
[TD]TAX RATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]city[/TD]
[TD]F[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9%[/TD]
[TD]LA[/TD]
[TD]9.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD]COUNTY[/TD]
[TD]LA[/TD]
[TD="align: right"]9.25%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9.50%[/TD]
[TD]SA[/TD]
[TD]8.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9%[/TD]
[TD]SB[/TD]
[TD]7.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD]OVERALL[/TD]
[TD][/TD]
[TD="align: right"]9.25%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]10%[/TD]
[TD]OC[/TD]
[TD]8.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]10.5&[/TD]
[TD]SL[/TD]
[TD]8%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mina91709]

With your tax table in columns A to D "city to look for" in H1, and county to look for in H2

Find city tax rate formula =VLOOKUP($H$1,$A$2:$B$20,2,FALSE)
find county tax rate formula =VLOOKUP($H$2,$C$2:$D$20,2,FALSE) (always returns county rate, even if city match exists)

single formula =IFERROR(VLOOKUP($H$1,$A$2:$B$20,2,FALSE),VLOOKUP($H$2,$C$2:$D$20,2,FALSE)) looks for city level match .. if that fails look for county level match

all formulas based on city-county data in rows 2 to 20 . adjust if needed

[TABLE="width: 742"]
<tbody>[TR]
[TD]CITY[/TD]
[TD]TAX RATE[/TD]
[TD]COUNTY[/TD]
[TD]TAX RATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]city[/TD]
[TD]F[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9%[/TD]
[TD]LA[/TD]
[TD]9.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD]COUNTY[/TD]
[TD]LA[/TD]
[TD="align: right"]9.25%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9.50%[/TD]
[TD]SA[/TD]
[TD]8.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9%[/TD]
[TD]SB[/TD]
[TD]7.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD]OVERALL[/TD]
[TD][/TD]
[TD="align: right"]9.25%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]10%[/TD]
[TD]OC[/TD]
[TD]8.50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]10.5&[/TD]
[TD]SL[/TD]
[TD]8%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thank you works well much appreciated

 

[dUBBINS]

With your tax table in columns A to D "city to look for" in H1, and county to look for in H2

Find city tax rate formula =VLOOKUP($H$1,$A$2:$B$20,2,FALSE)
find county tax rate formula =VLOOKUP($H$2,$C$2:$D$20,2,FALSE) (always returns county rate, even if city match exists)

single formula =IFERROR(VLOOKUP($H$1,$A$2:$B$20,2,FALSE),VLOOKUP($H$2,$C$2:$D$20,2,FALSE)) looks for city level match .. if that fails look for county level match

all formulas based on city-county data in rows 2 to 20 . adjust if needed

[TABLE="width: 742"]
<tbody>[TR]
[TD]CITY
[/TD]
[TD]TAX RATE
[/TD]
[TD]COUNTY
[/TD]
[TD]TAX RATE
[/TD]
[TD][/TD]
[TD][/TD]
[TD]city
[/TD]
[TD]F
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]9%
[/TD]
[TD]LA
[/TD]
[TD]9.25%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]COUNTY
[/TD]
[TD]LA
[/TD]
[TD="align: right"]9.25%
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]9.50%
[/TD]
[TD]SA
[/TD]
[TD]8.50%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]9%
[/TD]
[TD]SB
[/TD]
[TD]7.25%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]OVERALL
[/TD]
[TD][/TD]
[TD="align: right"]9.25%
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]10%
[/TD]
[TD]OC
[/TD]
[TD]8.50%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]10.5&
[/TD]
[TD]SL
[/TD]
[TD]8%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Nice, I forgot about IFERROR(); your solution is more elegant.

 

[mina91709]

Nice, I forgot about IFERROR(); your solution is more elegant.

THANK YOU BOTH FOR YOUR HELP
I GOT ANOTHER CHALLENGE WITH VLOOK UP OR ANOTHER FORMULA THAT WOULD WORK AND WAS HOPING YOU CAN HELP ME WITH,

FORMULA WILL LOOK UP THE DATE FROM MULTIPLE TABLES AND POPULATE 1ST AND 2ND VALUES FOR THE CATEGORiES BASED THE DATE VALIDATION NOTE IT WILL GO TO THE NEXT VALID DATE SEE EXAMPLE BELOW

[TABLE="width: 500"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]DATE INPUT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]7/15/2008[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]7/15/2008[/TD]
[/TR]
</tbody>[/TABLE]

GIVEN TABLES/LOOKUP TABLE
[TABLE="width: 500"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD] [/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]VALID TO DATE[/TD]
[TD] [/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]VALID TO DATE[/TD]
[TD] [/TD]
[TD]1ST VALUE[/TD]
[TD]2ND VALUE[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]80[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]5[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]120[/TD]
[TD]80[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]6/30/2009[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]8[/TD]
[TD]6/30/2008[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]12/31/2008[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]6/30/2009[/TD]
[/TR]
</tbody>[/TABLE]

 

[dUBBINS]

Okay, this will actually require a MATCH() / INDEX() solution. Because the date is to the right of the 1ST & 2ND VALUE Columns. And VLOOKUP() only works if the lookup value is in the furthest column to the left of what you are looking at. And for that reason, the rules of this forum require you to make a new thread. (pretty sure anyway).

Plus, you will get a wider variety of help with a new post. Enjoy your day. And if you don't get a reply on your new post, you can always private message either of us to take a look.