Help with IF statement with INDEX MATCH

Siobhan16

I need to write a formula to reference another sheet and then return "NEW HIRE REPLACEMENT" if that sheet has "New Appointment - new to UNCH" and also return a blank cell if there's nothing listed. I figured out the first part to return "NEW HIRE REPLACEMENT" but I can't find the blank.

Here's the first half of the formula-

=IF(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)) = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT")

Kelly Moore

Hey @Siobhan16

An IF statement should automatically return a blank if no further action is listed. Without a further action (ie, 'false action'), the IF doesn't know what to do so therefore it should do nothing, which is equivalent to a blank. What is this formula producing when the statement is not true?

If an error message appears, try this

=IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)),"") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT")

If you could provide more info on what happens when the IF statement is false, the community could assist.

Kelly Moore

Hey @Siobhan16

A nested IF will keep moving through the formula until the first 'true' is found. To nest, you stack the IF statements together so that the next IF statement forms the 'false' of the preceding one. Note how the closing parentheses of the IFs are at the end.

=IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)),"") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT",IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)), "") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT"))

Does this work for you?

Kelly

Marlana K.

Try the below, adding the value if false " ".

=IF(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)) = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT", " ")

Kelly Moore

Hey @Siobhan16

An IF statement should automatically return a blank if no further action is listed. Without a further action (ie, 'false action'), the IF doesn't know what to do so therefore it should do nothing, which is equivalent to a blank. What is this formula producing when the statement is not true?

If an error message appears, try this

=IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)),"") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT")

If you could provide more info on what happens when the IF statement is false, the community could assist.

Siobhan16

Thank you so much! Your formula did the trick. Mine was returning "#NO MATCH"

Marlana K.

@Siobhan16

Great! I also use IFERROR when indexing reason is the #NO MATCH is set by SS and we do not know what value they are using to set it. So when you have #NO MATCH it also prevents you from using conditional formatting. We use a lot of indexing to update over 700 Pharmacy Sites in tons of sheets and when a site closes it is removed from our Reference API so it cannot match the site # once it is removed I get the #NO MATCH so I set the " " or "Site Closed" or "VACANT" as the value if False.

Siobhan16

I have a follow up question. How do I nest another IF statement to search for a different phrase and return different text if that phrase is found.

With the current formula it returns "NEW HIRE REPLACEMENT" if INDEX MATCH finds "New Appointment - new to UNCH". I need to add to it to return "PROMOTION" if INDEX MATCH finds "Reappointment".

Current working formula:

=IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)), "") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT")

Kelly Moore

Hey @Siobhan16

A nested IF will keep moving through the formula until the first 'true' is found. To nest, you stack the IF statements together so that the next IF statement forms the 'false' of the preceding one. Note how the closing parentheses of the IFs are at the end.

=IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)),"") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT",IF(IFERROR(INDEX({RPA Action}, MATCH([Row ID]@row, {RPA ID}, 0)), "") = "New Appointment - new to UNCH", "NEW HIRE REPLACEMENT"))

Does this work for you?

Kelly

Siobhan16

Thanks for breaking down the nested if statement for me, @Kelly Moore. I was able to modify the formula for all the different scenarios and it worked.