INDEX #NO MATCH Formula Help

Michelle Fayed

Hello all,

Thank you in advance for any help!

I have the column formula =INDEX({Worker Contacts}, MATCH(Assign@row, {Worker Names}, 0)) that returns contacts for the individuals listed assign column. What can I add to the formula so that #NO MATCH does not appear if the assign column is blank? I want to keep #NO MATCH when the assign column contains names I need to add to my master index list. Any help is very welcome please.

Thank you kindly

Christina09

Hi @Michelle Fayed

Adding an IF formula will help achieve what you're trying to do:

=IF(ISBLANK(Assign@row), "", INDEX({Worker Contacts}, MATCH(Assign@row, {Worker Names}, 0)))

If the assigned cell is blank, it'll remain blank; if the assigned cell has a value, it'll run your index/match formula.

Michelle Fayed

Thank you @Christina Lam for responding.

I added what you recommended:

=IF(ISBLANK(Assign@row), "", INDEX({Worker Contacts}, MATCH(Assign@row, {Worker Names}, 0)))

However, I am still getting #NO MATCH returned.

Christina09

Are you getting #NO MATCH for blank cell?

Michelle Fayed

@Christina Lam yes I am still getting #NO MATCH for the blank cell

Christina09

I'm not sure why this is happening, can you screenshot the sheet and the formula?

Michelle Fayed

That is what I am seeing.

Christina09

It is showing #NO MATCH because the cell is not blank. I thought you're trying to write a formula that if the Asset Manager cell is blank, the Assign Ao cell will remain blank. if there is a no match from the index/match, it'll remain #no match.

Perhaps I misunderstood. Can you explain what are you trying to achieve?

Michelle Fayed

I am trying to write a formula for the Assigned To column. So if the Assign column is blank, the formula in the Assigned To column remains blank. In the image I sent, the Assign column (grey) is blank and the formula in the Assigned To column is returning #NO MATCH.

Christina09

Can you try this formula:

=IF(Assign@row="", "", INDEX({Worker Contacts}, MATCH(Assign@row, {Worker Names}, 0)))

Also try to save, and refresh the page

Michelle Fayed

Please know I am so grateful for your help! Unfortunately, that formula is also returning #NO MATCH.

Christina09

@Michelle Fayed sorry, I'm not sure why this is happening. Can @Genevieve P. help Michelle?

Genevieve P.

Thanks for the tag, @Christina Lam!

Hi @Michelle Fayed

Is it possible that the cell to the left isn't actually blank? Can you try the two following formulas to test how the formula is reading that cell:

=IF(Assign@row = "", "", "Not Blank")

Try also a simple:

=Assign@row

If this still hasn't helped, check the ranges you're referencing to see if they have any errors:

{Worker Contacts} and {Worker Names}

Formula errors will create dominos - if even one cell in either of those ranges says #NO MATCH then it will display in this formula referencing them.

To eliminate this, you'll want to wrap an IFERROR around your formulas to return the text "No Match" instead, like so:

=IFERROR(INDEX({Worker Contacts}, MATCH(Assign@row, {Worker Names}, 0)), "No Match")

Cheers,

Genevieve

Michelle Fayed

@Genevieve P.

Thank you so much. You were correct, the cell is not actually blank, apparently there are names there. Do you know why the names would be coming in from data shuttle like that?

Genevieve P.

Hi @Michelle Fayed

This depends on how you have your Data Shuttle workflow set up and what's in the source sheet for the matching rows.

Are the names values you would like to search for? Or is it a specific value like "No Name" that we could use in the IF statement as the rule to make the cell blank?