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

08/04/21
Accepted

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([email protected], {SUBST}, 1)), [Stage 8 FA]@row > INDEX({S8FA High}, MATCH([email protected], {SUBST}, 1))), "Red"))


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


Help :)


Thanks!!!

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

Answers

  • Paul NewcomePaul 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.

    thinkspi.com

  • 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 NewcomePaul 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.

    thinkspi.com

  • 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.

  • 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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

Sign In or Register to comment.