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 match not working

=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

 Airlines Class Heading Airlines1 e Airlines4 b Airlines6 c Airlines9 d Airlines5 e Airlines3 f Airlines7 g Airlines8 h Airlines2 i

below is State

 Airlines heading1 heading2 heading3 heading4 heading5 Airlines1 a b c d e Airlines2 f g h i b Airlines3 i c g h i Airlines4 b i a d e Airlines5 g h f g e Airlines6 c f b g a Airlines7 g f i a g Airlines8 h b h f a Airlines9 h g a f i
3 Replies
best response confirmed by pranusood (Copper Contributor)
Solution

# Re: index match not working

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)), "-")

# Re: index match not working

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, ""))

# Re: index match not working

this is too good.
1 best response

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

# Re: index match not working

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)), "-")