Need Help! INDEX with multiple criterias

[Bubbles_A]

Hi,

this is my current formula but I wanted to add more criterias as I have "NAME1, NAME2, NAME3, NAME4, NAME5" in a row for excel to return the correct value to me.

=IF(ISNUMBER(INDEX($AH$3:$FX$3,1,MATCH("NAME1",AH4:FX4,0)))=TRUE,INDEX($AH$3:$FX$3,1,MATCH("NAME1*",AH4:FX4,0)),INDEX($AH$3:$FX$3,1,MATCH("NAME2*",AH4:FX4,0)))

Appreciate if you could help to let me know how I should amend my formula.

Thanks!

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Bubbles_A]

Attachment as shown

[AliGW]

Where in the attachment is the formula?

[Bubbles_A]

It's at column B row 4 to 7.

[JohnTopley]

In row 5, there are 2 matches (AL/ML) (or 3 AL/ML/PTO ?) so expected result is ...???

[Bubbles_A]

I want multiple results.


in the event,
1) it match AL, return value of AL.
2) if no AL, return value of ML
3) if no AL or ML, return value of PTO


Sorry that I could not explain myself clearly. Hope you know what I mean. Thanks!

[JohnTopley]

... so there is a priority sequence i.e AL==>ML==>PTO==>??? so if both AL & ML are present, result is ONLY AL ??

and AL/PTO ???

Please add SAMPLE output your you posted file.

[Phuocam]

Try this ...

=IFERROR(INDEX($AH$3:$FX$3,1,MATCH("NAME1",AH4:FX4,0)),
IFERROR(INDEX($AH$3:$FX$3,1,MATCH("NAME1*",AH4:FX4,0)),
INDEX($AH$3:$FX$3,1,MATCH("NAME2*",AH4:FX4,0))))

[Bubbles_A]

Thanks @Phuocam! Your formula works for me even though I am not good in explaining in words. Appreciate!