Index and Match with multiple criteria

[Manny Singh]

Hello Masters,

Please see attached file for which I need some help putting Index and Match formula with multiple criteria.

When I select values from dropdown for Column 1 & 2, a formula looks up corresponding value in Column 3, 4 & 5.

I have put sample values in Column 3, 4 & 5 for example sake.

Tried Index and Match but multiple criteria are beyond my excel skills, would appreciate some help, please let me know if not clear.

Cheers, Manny.

 

[bosco_yip]

Try…..

In J15, formula copy across to L15 :

=IFERROR(INDEX(C5:C25,MATCH(SUBSTITUTE($H15,"_"," — "),$A5:$A24,0)-($H15<>"Pipe_Groove")+MATCH($I15,INDIRECT($H15),0)-3),"")

and, select J15:L15 >> Custom cell Format, in the Type box enter: ;;;@

Regards
Bosco

 

[bines53]

Hi,

Is there a problem in column A,Weld Type ,Getting listed first in each section?
So more efficiently, and can be avoided volatile functions(INDIRECT).
And what version of Excel you use ?

David

 

[Manny Singh]

Try…..

In J15, formula copy across to L15 :

=IFERROR(INDEX(C5:C25,MATCH(SUBSTITUTE($H15,"_"," — "),$A5:$A24,0)-($H15<>"Pipe_Groove")+MATCH($I15,INDIRECT($H15),0)-3),"")

and, select J15:L15 >> Custom cell Format, in the Type box enter: ;;;@

Regards
Bosco

Hi Bosco,

I appreciate your help and like your skills.

Just few queries if you can respond please:

what's role of function ($H15<>"Pipe_Groove")?
what's role of indirect function MATCH($I15,INDIRECT($H15),0)-3)?
And custome cell formatting ;;;@?

I am learning slowly and if you could explain these functions, would be very helpful.

 

[bosco_yip]

Hi Bosco,

I appreciate your help and like your skills.

Just few queries if you can respond please:

what's role of function ($H15<>"Pipe_Groove")?
what's role of indirect function MATCH($I15,INDIRECT($H15),0)-3)?
And custome cell formatting ;;;@?

I am learning slowly and if you could explain these functions, would be very helpful.

The posted formula is a simplified from this one

=IFERROR(INDEX(C5:C25,MATCH(SUBSTITUTE($H15,"_"," — "),$A5:$A24,0)-($H15<>"Pipe_Groove")-2+MATCH($I15,INDIRECT($H15),0)-1),"")

into this one

=IFERROR(INDEX(C5:C25,MATCH(SUBSTITUTE($H15,"_"," — "),$A5:$A24,0)-($H15<>"Pipe_Groove")+MATCH($I15,INDIRECT($H15),0)-3),"")

Formula explanation :

1] This part : "...-($H15<>"Pipe_Groove")-2... " --> result will give -2 or -3. Row Position adjusting in respect of the 1st criteria (Weld Type) .

2] This part : "...MATCH($I15,INDIRECT($H15),0)-1... " --> Row Position adjusting in respect of the 2nd criteria (Position) .

3] Custom cell formatting ;;;@ --> to remove 0

Regards
Bosco