CODEWITHSUNDEEP
excelmatch
Hulk Smash 93
Hulk Smash 93

Reputation: 55

Index and Match formula with multiple Lookup Values but for one cell

Im looking for some help with a formula for Index and Match.

=INDEX(F$2:AE$2,MATCH(1,F20:AE20,0))

This formula will match the 1st cell that has a number 1. But I also want it to look and count if the cell has either a 1, OT, or TR. And then pull the corresponding value.

Any help would be greatly appreciated.

Upvotes: 0

Views: 65

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Give this a try:

=INDEX(F$2:AE$2,MIN(IFERROR(MATCH(1,F20:AE20,0),9999),IFERROR(MATCH("OT",F20:AE20,0),9999),IFERROR(MATCH("TR",F20:AE20,0),9999)))

It should return the header associated with the first occurrence of either 1 or OT or TR

Note:

There is a much simplier formula, but it is an array formula.

Upvotes: 1

Related Questions