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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product Name
[/TD]
[TD]500g
[/TD]
[TD]100g
[/TD]
[TD]10g
[/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD]$50
[/TD]
[TD]$20
[/TD]
[TD]$5
[/TD]
[/TR]
[TR]
[TD]Product 2
[/TD]
[TD]$69
[/TD]
[TD]$30
[/TD]
[TD]$7
[/TD]
[/TR]
</tbody>[/TABLE]

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>[TABLE="width: 500"]
<tbody>[TR]
[TD]

Product 1​

<strike></strike>
[/TD]
[TD]

100g​

​

[/TD]
[TD]$20
[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]

Product Name​

<strike></strike>
[/TD]
[TD]

500g​

[/TD]
[TD]

100g​

<strike></strike>
[/TD]
[TD]

10g​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]

Product 1​

<strike></strike>
[/TD]
[TD]

$50​

[/TD]
[TD]

$20​

<strike></strike>
[/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]

Product 2​

[/TD]
[TD]<strike></strike>$69
[/TD]
[TD]$30<strike></strike>
[/TD]
[TD]

$7​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]dropdown selection 1[/TD]
[TD]dropdown selection2
[/TD]
[TD]<strike></strike>Result
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]

Product 2​

<strike></strike>
[/TD]
[TD]

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>
[/TD]
[TD]$30
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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