Having more than one 'match' in an iferror-index-match formula

[Sedgeway]

Hello,

I'm using the following IFERROR formula for in a spreadsheet:

=IFERROR(INDEX(AK$5:AO$5,1,MATCH("NIGHT",AK43:AO43,0)),"UNASSIGNED")

As you can see, the lookup value is "NIGHT". I want to add an additional lookup value: "NIGHT shift swap".

I've tried:

=IFERROR(INDEX(AK$5,AO$5,1,MATCH("NIGHT",AK43:AO43,0),MATCH("NIGHT shift swap",AK43:AO43,0)),"UNASSIGNED")

but it's obviously wrong, as it's not working.

Grateful if someone could point out my formula error here.

(PS - I usually attach a spreadsheet, but this one is massive and confidential, so hoping the above explanation is sufficient)

Thanks in advance!

[6StringJazzer]

I want to add an additional lookup value: "NIGHT shift swap".

Does this mean that you want to return two answers from one formula? Or that you want to look for "NIGHT" and it you don't find it, look for "NIGHT shift swap"? What result do you want to see?

[Sedgeway]

Hi Jeff,

To clarify, I want to look for 'NIGHT' and if not found, look for 'NIGHT shift swap'. And if neither are found, then 'UNASSIGNED' is returned (if either are found, then a person's name designated to a column is returned - that's working fine with the current formula, when 'NIGHT' is found)

Hope that makes sense!

[FlameRetired]

What if MATCH finds both?

If "NIGHT" is the preferred match perhaps this will do.

=IFERROR(INDEX(AK$5:AO$5,1,LOOKUP(1E+306,MATCH({"Night shift swap","NIGHT"},AK43:AO43,0))),"UNASSIGNED")

[Sedgeway]

Thanks Dave, that's worked! Much appreciated!

[FlameRetired]

You're welcome. Thanks for the feedback and marking your thread Solved.