CODEWITHSUNDEEP
excel
user10901042
user10901042

Reputation: 13

Excel: Index and match with single criteria with multiple coulmns/rows

Group  A  B  C  D  E
       1  2  3  4  5
       6  7  8  9  10
       11 12 13 14 15

I am looking for a index and match formula which I can show "9" belongs to the group "D". Index(A1:E1,match("9",A2:E4,0)) Match can only search one column or row. Are there anyways to search multiple columns/rows?

Thank you,

Upvotes: 1

Views: 74

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use AGGREGATE:

=INDEX(1:1,AGGREGATE(15,7,COLUMN(A2:E4)/(A2:E4 = 9),1))

Upvotes: 4

Related Questions