Help with multiple filters

[game_federer]

Hi Sirs

Table III has data on the quantity ordered for certain products. These products have a product code along with a classifier L or R. There are two records for each product code, one for L and the other for R.
The user will enter the product code and the classifier (L or R) in the cells G3 and G4 respectively. Write two formulas in the box provided below which will return:

a) the correct quantity for the combination of the product code and the classifier. For example, if the user enters P2-200 and L in G3 and G4 respectively, your formula should return 20.

b) the status of the combination, for the above example, the answer would be P.

I was able to do the first part but cant seem to think how to do the second part.
Your help will be greatly appreciated.

 

[Mix1325]

You can use a helper column to solve the status. The formula is included below. Also see attachment.

=INDEX(A3:E13,MATCH(G3&G4,A3:A13,0),MATCH(E3,A3:E3,0))

 

[bosco_yip]

Or, without helper column and using IBM Database function which is faster than all Excel functions.

In I3, formula copied right to J3

=DGET($B3:$E13,I2,$G2:$H3)

Regards
Bosco

 

[bines53]

It is also possible,

=INDEX(E4:E13,MATCH(1,--(MMULT(--(B4:D13=G3:I3),{1;1;1})=3),0)) or

=INDEX(E4:E13,MATCH(1,INDEX((B4:B13=G3)*(C4:C13=H3)*(D4:D13=I3),),0))

David

 

[bosco_yip]

It is also possible,

=INDEX(E4:E13,MATCH(1,--(MMULT(--(B4:D13=G3:I3),{1;1;1})=3),0)) or

=INDEX(E4:E13,MATCH(1,INDEX((B4:B13=G3)*(C4:C13=H3)*(D4:D13=I3),),0))

David

The 1st formula can be shortened to :

=INDEX(E4:E13,MATCH(3,MMULT(--(B4:D13=G3:I3),{1;1;1}),0))

Regards
Bosco