Can IF be combined with INDEX/MATCH AND ISBLANK to provide alternate value for blank results

Erin L Foley

I can successfully use INDEX/MATCH to look up values between sheets, but many of the results are blank, in which case I need to INDEX/MATCH to a different column of data. Is it possible to use an IF(ISBLANK), perhaps in a nested IF to return the 2nd-choice cell?

For example:

=IF(INDEX({Directory VP}, MATCH(EmployeeEmail@row, {Directory Email}, 0)), IF(ISBLANK(INDEX({Directory Supervisor}, MATCH(EmployeeEmail@row, {Directory Email}, 0)))))

EmployeeEmail@row = search value to look in the Directory Sheet for related VP information

{Directory VP} = the column in the Directory Sheet that contains my preferred result, the employee's VP

{{Directory Supervisor} = the column in the Directory Sheet that contains the employee's Supervisor name, in case the VP result is not available (e.g., if the employee is already a high-level VP)

Genevieve P.

Hey @Erin L Foley

Yes, you can do this! 🙂

However you would need to write 3 Index(Match combinations: 2 the same, 1 the alternative

This is the structure:

If the first formula is not blank, then complete the first formula. Otherwise, do the second formula.

=IF(first formula <> "", first formula, second formula)

=IF(INDEX(MATCH(first) <> "", INDEX(MATCH(first), INDEX(MATCH(second)))

Or in your case,

=IF(INDEX({Directory VP}, MATCH(EmployeeEmail@row, {Directory Email}, 0)) <> "", INDEX({Directory VP}, MATCH(EmployeeEmail@row, {Directory Email}, 0)), INDEX({Directory Supervisor}, MATCH(EmployeeEmail@row, {Directory Email}, 0)))

Cheers,

Genevieve