Forum Discussion
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 |
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)), "-")
3 Replies
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.
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)), "-")
