Broken Index/Match formula

Question

We have a spread sheet that auto populates labor hours based on a series of drop down lists.&nbsp; For one the formula works perfectly for every drop down list option except one.&nbsp; The one that does not work returns a #N/A value in each square. The formula is the same for every cell, but I can't for the life of me figure out why the one category returns an error but the others don't.&nbsp; Thank you in advance for any help or suggestions, I've already tried to go through all the troubleshooting steps provided with Excel 2016 Help
&nbsp;
Here's the formula:
{=INDEX(Index!$I$4:$I$3109,MATCH(1,(Estimate!$K10=Index!$B$4:$B$3109)*(Estimate!$L10=Index!$C$4:$C$3109)*(Estimate!$M10=Index!$D$4:$D$3109)*(Estimate!$N10=Index!$E$4:$E$3109)*(Estimate!$O10=Index!$F$4:$F$3109)*(Estimate!$P10=Index!$G$4:$G$3109),0))}

detlef_lewin · Answer

Chuck,
&nbsp;
your filters in K10:P10 don't match any records in B4:G3109.
&nbsp;
If you think there should be a match check the spelling and look for trailing spaces.
&nbsp;

chuck davis · Answer

Could you elaborate as to why this would work for other drop down categories, but not one specific one?&nbsp; It's the same formula but based on the data list and the item chosen off those lists it generates a labor hours return.&nbsp; I've attached screen shots of the table working, not working, and data index.

tim heng · Answer

I agree with Detlef - check that row in your Data Index sheet, and check for any blank spaces that might sit in one of those six columns that you're looking up.&nbsp; Chances are, there's a rogue blank space that is causing the match to fail.
&nbsp;
If the blank space was in the input sheet, then it's likely other combinations would fail too.&nbsp; My guess is that it's in the data, and you can narrow it down to that specific row (or any given row that's failing).
&nbsp;
Edit: Another way you can check this -&nbsp;in a single line, just do a simple "=I4=Index!B726" (I think I've got the cell references right from your screenshots) and copy it to the right to check the other columns. &nbsp;The one that's coming up as FALSE is the one that you'll need to fix somewhere.

willy lau · Answer

They are saying that
=MATCH("ABC", {"ABC "}, 0)
will give out #N/A.
&nbsp;
"Could you elaborate as to why this would work for other drop down categories, but not one specific one?&nbsp;&nbsp;"
I just guess that it is "Quarterly " at the row you highlighted in the data index sheet. Matching "Triannual" correctly doesn't mean that other data is correctly spelled in data index sheet.&nbsp;
&nbsp;
If you study what yourself had just said "It's the same formula but based on the data list and the item chosen off those lists it generates a labor hours return." you can know what is happening.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

kelli davis · Answer

There is much more “broken “....and about to become “unmatched “ than some folks will let themselves believe!!! Something has to give!! It’s way past due,don’t ya think?!?!😢

Forum Discussion

Broken Index/Match formula

5 Replies

Resources