INDEX #NO MATCH Formula Help
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
Answers
-
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.
-
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.
-
Are you getting #NO MATCH for blank cell?
-
@Christina Lam yes I am still getting #NO MATCH for the blank cell
-
I'm not sure why this is happening, can you screenshot the sheet and the formula?
-
That is what I am seeing.
-
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?
-
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.
-
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
-
Please know I am so grateful for your help! Unfortunately, that formula is also returning #NO MATCH.
-
@Michelle Fayed sorry, I'm not sure why this is happening. Can @Genevieve P. help Michelle?
-
Thanks for the tag, @Christina Lam!
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!