Getting a NO MATCH when using Index/Match in a formula

Elizabeth Aird

This has been working very well on previous sheets. This is a Titration Log and it varies per plant and therefore very tailored to their line and parameters. In the steel business - so you'll see substrate as to what I am matching.

I have a column that does the look up and returns "red" if out of spec. The ALUM formula is not working.

=IF(ISBLANK([Stage 8 FA]@row), "", IF(OR([Stage 8 FA]@row < INDEX({S8FA Low}, MATCH(Substrate@row, {SUBST}, 1)), [Stage 8 FA]@row > INDEX({S8FA High}, MATCH(Substrate@row, {SUBST}, 1))), "Red"))

This is the sheet it uses to look at the specs (INDEX):

Help :)

Thanks!!!

Paul Newcome

The 1 indicates a close match whereas the 0 indicates an exact match. I'm sorry, but that should have been one of the first things I suggested. My apologies for not catching that sooner.

Paul Newcome

If the others are working and it is just "ALUM" that is giving you issues, double check that the cell data is an exact match. If you have a space at the end of one and not at the end of the other, it may not be matching even though they appear to be the same. A good idea to double check this is to copy/paste from the lookup table to the data sheet so that you can ensure it is definitely an exact match. If that doesn't work, then there may be a bit of a bug in your sheet.

Elizabeth Aird

I think it's a MATCH issue. Looking at the Table above (where the columns are green), I should return a "No match" for CTGL (or it remains blank) since CTGL does not have any specs. It seems that it is just looking at the specs and not the substrate.

Paul Newcome

But there is a match for CTGL since it is listed in the Substrate column. Neither of them should be returning that particular error.

What happens if you enter the same data on another row in the High/Low sheet? Pick a new row that has never been used for anything and reenter the ALUM data across this row.

If that doesn't work, then try removing everything except for a singe MATCH function and have one pointing at CTGL and another pointing at ALUM.

Elizabeth Aird

didn't work - I am getting the same response back. Can you tell me the difference between the "1" at the end of those formulas vs using a "0"...

the CTGL is listed - but it shouldn't come back as red since it is blank.

This is making me dizzy lol

I did use another sheet and did a "save as"... did the same for the spec sheet.

When I go back to the original sheet, i noticed that the ALUM was giving this "no match" as well. That particular facility doesn't run aluminum very often so I didn't catch it.

Elizabeth Aird

GLUM and ALUM - I changed to XXXX and YYYY and it works. There is something with these words.

If I write the words out (Galvalume and Aluminum) it still doesn't work. I don't get it.

Elizabeth Aird

I used a test sheet and deleted the other 3 substrates out - leaving only ALUM and GLUM. I got it to work. I did notice that the hyperlinks below didn't match, so I updated those as well. That worked.

Updated the formula to go back to using the preferred sheet for specs, bam! Doesn't work again.

I moved ALUM and GLUM rows to the top. Suddenly, it works. I have no idea why.. so strange!!!

Your help is ALWAYS appreciated!!

Paul Newcome

The 1 indicates a close match whereas the 0 indicates an exact match. I'm sorry, but that should have been one of the first things I suggested. My apologies for not catching that sooner.

krieves

*Update! The solution to my issue was adding "FALSE" to the end of the formula. Amazing.

I am having a very similar issue :( How do I go about addressing potential bugs in my sheet? I have a list of ten items using the same VLOOKUP formula, but three of them are returning a #NOMATCH error. I have copied/pasted to make sure everything is exactly the same, but I am still getting the error. I'm pulling my hair out!

Paul Newcome

@krieves Glad you got it sorted.

"TRUE" is the default if you do not enter anything into that portion of the VLOOKUP and only looks for a close match which (as you have found) isn't always reliable whereas "FALSE" looks for an exact match.