INDEX/MATCH with Infinite possibilites

[olimajor123]

Hi,

Was not sure what to name the topic to best describe this.

I have an INDEX, MATCH formula that looks up a list of categories to return a response time based on the category. This works fine. However I have an issue that if a person can't be allocated into a category, it automatically uses their name as the category, which presents infinite possibilities for the category name. I am looking to write into the formula something that says 'IF you can't find the category in the list, then assume the category is "Patient Moves"'

My formula is currently,

=IFERROR(INDEX(SLA!$C$3:$C$160,MATCH(Data!E2,SLA!$A$3:$A$160,0)),"")

where SLA!C is the Category to return from the table
and DATA!E is the category to look up

 

[Jonmo1]

Try

=IFERROR(INDEX(SLA!$C$3:$C$160,MATCH(Data!E2,SLA!$A$3:$A$160,0)),"Patient Moves")

 

[olimajor123]

Try

=IFERROR(INDEX(SLA!$C$3:$C$160,MATCH(Data!E2,SLA!$A$3:$A$160,0)),"Patient Moves")

Thanks, I literally just realised this myself after posting but good to know it seems like the correct way

 

[Jonmo1]

You're welcome.

Strictly speaking, it's returning "Patient Moves" upon receiving ANY error. Not just the #N/A that ocurs when the match isn't found.

To be specific to that error, it would be

=IF(ISNA(MATCH(Data!E2,SLA!$A$3:$A$160,0)),"Patient Moves",INDEX(SLA!$C$3:$C$160,MATCH(Data!E2,SLA!$A$3:$A$160,0)))