Forum Discussion

APagels's avatar
APagels
Copper Contributor
May 31, 2024

Match function returns the same index for multiple non-equal values

I'm working on a digital design project, using Excel to help sort my data entries to see what elements I can eliminate.  What this means:

I have a list of 800 values in two lists that are all strings of text, not arranged in parallel order.  I've used VLOOKUP() to allocate the values from one list that match the other, but now I want to remove those values from the source list.  I've used the following two functions:

 

This returns the text string I'm looking for, and I've verified it is exactly what I'm looking for, in every cell down the list.

=VLOOKUP("*"&C2&"*",A2:A413,1,FALSE)

 

This returns the index of the above text string ...well, it's supposed to, and it does sometimes.  But, not always:

=MATCH(E2,$A2:$A413,0)+1

 

Attached is a snippet for reference.  I assume MATCH is struggling with the apostrophes, but then wouldn't it just return 2 for everything?

 

ETA:

I wrote a formula to compare the value in the returned cell from MATCH with the value in E (the matching value from semi-random position in A), and it's only TRUE for the first line.  MATCH is not working as implemented.

 

Excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    May 31, 2024

    APagels 

    The absolute referencing seems to be wrong in your formulas. Try it out:

     

    =VLOOKUP("*"&C2&"*",A$2:A$413,1,FALSE)
=MATCH(E2,A$2:A$413,0)+1

New alternatives from VLOOKUP())
=XLOOKUP("*"&C2:C14&"*",A2:A14,A2:A14,"",2)
=IFERROR(XMATCH(H2#,A2:A14)+1,"")

without Helpcolumn:
=IFERROR(XMATCH("*"&C2:C14&"*",A2:A14,2)+1,"")

     

     

     

Resources