Index Match Else

Tina Ozturk

Greeting All,

I have created a simple index match that successfully matches state/province from my reference datasheet to pull in the corresponding district.

I would love to indicate "N/A" for the instances when a state is blank but I cannot figure out the argument.

=INDEX({District}, MATCH([State/Prov]@row, {Reference Datasheet State/Prov}, 0))

Thank you in advance for any help.

Tina

Tina Ozturk

Thanks J. I attempted adding string suffix that you suggested, but it did not work.

Maybe an if/then will work?

AnalyticOwl

Hello Tina!

Have you tried to encapsulate your formula with and IFERROR

=IFERROR(INDEX({District}, MATCH([State/Prov]@row, {Reference Datasheet State/Prov}, 0)),"N/A")

Maybe this could work!

Hope this could be useful, good vibes for you!

J

Tina Ozturk

Thanks J. I attempted adding string suffix that you suggested, but it did not work.

Maybe an if/then will work?

Tina Ozturk

Adding the if statement did work!!

=IF([State/Prov]@row <> "", INDEX({District}, MATCH([State/Prov]@row, {Reference Datasheet State/Prov}, 0)), "N/A")

Thanks J for your inspiration.

Cheers,

Tina