If / and lookup

[drluke]

Attempting to lookup by product from a data table arranged like:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]3100[/TD]
[TD]3200[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]Cust A[/TD]
[TD]25[/TD]
[TD]125.26[/TD]
[TD]10.26[/TD]
[TD]1000.00[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]80[/TD]
[TD]589.67[/TD]
[TD]13.59[/TD]
[TD]3000.00[/TD]
[/TR]
[TR]
[TD]Cust C[/TD]
[TD]90[/TD]
[TD]625.71[/TD]
[TD]19.99[/TD]
[TD]5600.00[/TD]
[/TR]
[TR]
[TD]Cust D[/TD]
[TD]100[/TD]
[TD]228.14[/TD]
[TD]8.56[/TD]
[TD]9000.00[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to lookup the value of sales for product codes (col C-E) if the customer code in col B is xx:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Prod Code[/TD]
[TD]C Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]3100[/TD]
[TD]100[/TD]
[TD]228.14[/TD]
[/TR]
[TR]
[TD]3300[/TD]
[TD]80[/TD]
[TD]3000.00[/TD]
[/TR]
</tbody>[/TABLE]

Any advise appreciated.

 

[Vbagreenhorn1]

try INDEX in combination with the MATCH formulas for the row and column number.

 

[drluke]

Thanks! I don't know how to do that.

 

[Vbagreenhorn1]

ok, assuming the values to lookup are in A9 (3100) and in B9(100) and the table you posted to find the value is in A1:E5.

=INDEX($B$1:$E$5,MATCH(B9,$B$1:$B$5,0),MATCH(A9,$B$1:$E$1,0))

 

[drluke]

Great - thank you!!