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:

CITY	TAX RATE	COUNTY	TAX RATE
A	9%	LA	9.25%
B	9.5%	SA	8.5%
C	9%	SB	7.25%
D	10%	OC	8.5%
E	10.5&	SL	8%

<tbody>
</tbody>

CELLS TO MATCH AND MATCH INPUT FROM THE LOOK UP TABLES:

CITY	A
COUNTY	LA
TAX RATE	RETURN VALUE SHOULD BE THE VALUE OF THE CITY WHICH IS %9 AND IGNORE THE COUNTY

<tbody>
</tbody>

CITY	F
COUNTY	LA
TAX RATE	RETURN VALUE SHOULD BE THE COUNTY BECAUSE THE CITY IS NOT LISTED. VALUE WILL BE %9.25

<tbody>
</tbody>

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

CITY	TAX RATE	COUNTY	TAX RATE			city	F	#N/A
A	9%	LA	9.25%			COUNTY	LA	9.25%
B	9.50%	SA	8.50%
C	9%	SB	7.25%			OVERALL		9.25%
D	10%	OC	8.50%
E	10.5&	SL	8%

<colgroup><col span="4"><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[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

CITY	TAX RATE	COUNTY	TAX RATE			city	F	#N/A
A	9%	LA	9.25%			COUNTY	LA	9.25%
B	9.50%	SA	8.50%
C	9%	SB	7.25%			OVERALL		9.25%
D	10%	OC	8.50%
E	10.5&	SL	8%

<tbody>
</tbody>

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

CITY	TAX RATE	COUNTY	TAX RATE			city	F	#N/A
A	9%	LA	9.25%			COUNTY	LA	9.25%
B	9.50%	SA	8.50%
C	9%	SB	7.25%			OVERALL		9.25%
D	10%	OC	8.50%
E	10.5&	SL	8%

<tbody>
</tbody>

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

CATEGORY	1ST VALUE	2ND VALUE	DATE INPUT
A	10	10	7/15/2008
B	20	20	7/15/2008
C	30	30	7/15/2008
D	40	40	7/15/2008
E	50	50	7/15/2008

<tbody>
</tbody>

GIVEN TABLES/LOOKUP TABLE

CATEGORY		1ST VALUE	2ND VALUE	VALID TO DATE		1ST VALUE	2ND VALUE	VALID TO DATE		1ST VALUE	2ND VALUE	DATE
A		15	20	6/30/2008		10	10	12/31/2008		30	80	6/30/2009
B		60	5	6/30/2008		20	20	12/31/2008		100	50	6/30/2009
C		5	8	6/30/2008		30	30	12/31/2008		120	80	6/30/2009
D		7	9	6/30/2008		40	40	12/31/2008		50	100	6/30/2009
E		200	8	6/30/2008		50	50	12/31/2008		100	50	6/30/2009

<tbody>
</tbody>

 

[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.