Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

index match not working

Copper Contributor

=INDEX(state!$B$1:$F$1,MATCH(contra!A2,state!$A$2:$A$10,0),MATCH(contra!B2,state!B2:F2,0))

 

I have 2 data sets state and contra and I am trying to retrieve heading from state. but this is not working.

Below is Contra

AirlinesClassHeading
Airlines1e 
Airlines4b 
Airlines6c 
Airlines9d 
Airlines5e 
Airlines3f 
Airlines7g 
Airlines8h 
Airlines2i 

 

below is State

 

Airlinesheading1heading2heading3heading4heading5
Airlines1abcde
Airlines2fghib
Airlines3icghi
Airlines4biade
Airlines5ghfge
Airlines6cfbga
Airlines7gfiag
Airlines8hbhfa
Airlines9hgafi
3 Replies
best response confirmed by pranusood (Copper Contributor)
Solution

@pranusood 

Use

 

=IFERROR(INDEX(State!$B$1:$F$1, MATCH(B2, INDEX(State!$B$2:$F$10, MATCH(A2, State!$A$2:$A$10, 0), 0), 0)), "-")

@pranusood 

For Airlines7 and Airlines8 there are 2 matches. To return both of them:

 

=FILTER(State!$B$1:$F$1, FILTER(State!$B$2:$F$10, State!$A$2:$A$10=A8, "")=B8, "")

 

or to return the result in one cell:

 

=TEXTJOIN(", ", TRUE, FILTER(State!$B$1:$F$1, FILTER(State!$B$2:$F$10, State!$A$2:$A$10=A2, "")=B2, ""))

this is too good.
1 best response

Accepted Solutions
best response confirmed by pranusood (Copper Contributor)
Solution

@pranusood 

Use

 

=IFERROR(INDEX(State!$B$1:$F$1, MATCH(B2, INDEX(State!$B$2:$F$10, MATCH(A2, State!$A$2:$A$10, 0), 0), 0)), "-")

View solution in original post