VLOOKUP or MATCH w/ Multiple Columns - Return 1 Result

[JGarcia]

Looking for help with below, sorry if post is too basic. Am familiar with VLOOKUP but not with multiple columns. STATE and PRODUCT fields in the SELECTION section below are just Data Validation List from the TABLE below.

Once STATE and PRODUCT are selected, can VLOOKUP return the correct Price from the TABLE?

SELECTION:

STATE	AL
PRODUCT	Product 1
RATE	?

<tbody>
</tbody>

TABLE:

	RATES
STATE	Product 1	Product 2	Product 3
AL	3.00	4.00	5.00
CO	4.00	5.00	6.00
GA	5.00	6.00	7.00
IL	6.00	7.00	8.00
IN	7.00	8.00	9.00
KS	8.00	9.00	10.00
KY	9.00	10.00	11.00
LA	10.00	11.00	12.00
MS	11.00	12.00	13.00
NC	12.00	13.00	14.00
NY	13.00	14.00	15.00
OH	14.00	15.00	16.00
PA	15.00	16.00	17.00
TN	16.00	17.00	18.00
TX	17.00	18.00	19.00

<tbody>
</tbody>

 

[jarjarbingie]

hello JGarcia,

try this:

=OFFSET($A$2,MATCH($H$3,$A$3:$A$17,0),MATCH($H$4,$B$2:$D$2,0))

where:
A2 - start of the reference table
H3 - state input
H4 - product input

 

[T. Valko]

Looking for help with below, sorry if post is too basic. Am familiar with VLOOKUP but not with multiple columns. STATE and PRODUCT fields in the SELECTION section below are just Data Validation List from the TABLE below.

Once STATE and PRODUCT are selected, can VLOOKUP return the correct Price from the TABLE?

SELECTION:

STATE	AL
PRODUCT	Product 1
RATE	?

<TBODY>
</TBODY>

TABLE:

	RATES
STATE	Product 1	Product 2	Product 3
AL	3.00	4.00	5.00
CO	4.00	5.00	6.00
GA	5.00	6.00	7.00
IL	6.00	7.00	8.00
IN	7.00	8.00	9.00
KS	8.00	9.00	10.00
KY	9.00	10.00	11.00
LA	10.00	11.00	12.00
MS	11.00	12.00	13.00
NC	12.00	13.00	14.00
NY	13.00	14.00	15.00
OH	14.00	15.00	16.00
PA	15.00	16.00	17.00
TN	16.00	17.00	18.00
TX	17.00	18.00	19.00

<TBODY>
</TBODY>

One way...

Book1
	A	B	C	D
1	STATE	Product 1	Product 2	Product 3
2	AL	3	4	5
3	CO	4	5	6
4	GA	5	6	7
5	IL	6	7	8
6	IN	7	8	9
7	KS	8	9	10
8	KY	9	10	11
9	LA	10	11	12
10	MS	11	12	13
11	NC	12	13	14
12	NY	13	14	15
13	OH	14	15	16
14	PA	15	16	17
15	TN	16	17	18
16	TX	17	18	19
17	_	_	_	_
18	STATE	AL	_	_
19	PRODUCT	Product 1	_	_
20	RATE	3	_	_
Sheet1

This formula entered in B20:

=VLOOKUP(B18,A1:D16,MATCH(B19,A1:D1,0),0)

 

[JGarcia]

Thanks for example Biff, but I get a formula error, Excel won't accept and recommends change below where it adds a multiply operator at the end.

=VLOOKUP(B18,A1:D16,MATCH(B19,A1:D1,0)*0)

 

[JGarcia]

Thanks for response. Formula is accepted but just returns #N/A.

 

[T. Valko]

Thanks for response. Formula is accepted but just returns #N/A.

That means either the lookup value can't be found:

=VLOOKUP(B18,A1:D16

Or, the Product name can't be found:

MATCH(B19,A1:D1,0)

See this for descriptions of common lookup problems:

http://contextures.com/xlFunctions02.html#Trouble

 

[JGarcia]

Got it, thanks!

 

[T. Valko]

Got it, thanks!

Good deal. Thanks for the feedback!