If / and lookup

[drluke]

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

		3100	3200	3300
Cust A	25	125.26	10.26	1000.00
Cust B	80	589.67	13.59	3000.00
Cust C	90	625.71	19.99	5600.00
Cust D	100	228.14	8.56	9000.00

<tbody>
</tbody>

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:

Prod Code	C Code	Value
3100	100	228.14
3300	80	3000.00

<tbody>
</tbody>

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!!