How to use multiple Index and Match in the multiple conditions

[zamal264]

Hi,

I'm having a difficulty in finding the right Index and Match formula to match several variables either vertically and horizontally.
Thank you so much,

[AliGW]

Well, from your description in the workbook, this should work:

=IFNA(INDEX(Repair!$F$2:$F$7,MATCH(A2,Repair!$A$2:$A$7,0)),IF(C2="SYS",INDEX(PN!$E$2:$E$7,MATCH(A2,PN!$D$2:$D$7,0)),B2))

Howver, thre are no possible matches in the PN table, so you are going to need to tell us what the expected outcomes are.

[zamal264]

Thank you AliGW,

=IFNA(INDEX(Repair!$F:$F,MATCH(Data!A4,Repair!$A:$A,0)),IF(Data!C4="SYS",INDEX(PN!$B$2:$G$7,MATCH(Data!D4,PN!$B$1:$G$1,0),MATCH(Data!E4,PN!$A$2:$A$7,0)),Data!B4))

I reattached the sample1 - somehow column G4 returns as 0 which was supposed to be P19087-02 not sure why and how do I change #N/A in column G7 to return as blank instead.

Thanks again,

[JeteMc]

Try the following modification the formula:

Please Login or Register  to view this content.

Let us know if you have any questions.