Getting value from range based on 2 dropdowns

[oh_neil]

Hi Guys
I have the below table which is used as a data set to populate a second table (All on the same work sheet)

Product Name	500g	100g	10g
Product 1	$50	$20	$5
Product 2	$69	$30	$7

<tbody>
</tbody>

My second table has a 2 dropdown list

1. The first which is populated with the Product Names
2

. The second which is populated with the Product weight



​

Based on the dataset above - When the user select Product 1 (from 1st dropdown) and 100g (from 2nd dropdown) I would like to populate a cell with $20. Please help

​

<strike></strike>

Product 1​ <strike></strike>

100g​ ​

$20

<tbody>
</tbody>

 

[Joyner]

Hello and welcome to the board. Try this:

=INDEX(B3:D4,MATCH(A1,A3:A4,0),MATCH(B1,B2:D2,0))

Where prod table is in A1, weight is B1 and your table starting at A2

 

[oh_neil]

Hi thanks for replaying
I applied the formula as you stated but I'm getting an error. Below is my actual layout and the expected result​

	A	B	C	D	E
1	Product Name​ <strike></strike>	500g​	100g​ <strike></strike>	10g​
2	Product 1​ <strike></strike>	$50​	$20​ <strike></strike>	$5​
3	Product 2​	<strike></strike>$69	$30<strike></strike>	$7​ <strike></strike>	<strike></strike>
4
5	dropdown selection 1	dropdown selection2	<strike></strike>Result
6	Product 2​ <strike></strike>	100g​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>	$30
7

<tbody>
</tbody>

 

[Joyner]

For that layout this should work:

=INDEX(B2:D3,MATCH(A6,A2:A3,0),MATCH(B6,B1:D1,0))

 

[oh_neil]

I must be doing something silly. I'm still getting error. If you have a few minutes please recreate and test it please

 

[Joyner]

I did test, are you getting #N/A? Are you sure your lookup values and the table values are the same? Product 1 = Product 1 ; 100g = 100g etc.? If you do for example A6=A3 B6=C1 do you get TRUE?