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

Copper Contributor

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?

APagels_0-1717138314453.png

 

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.

 

1 Reply

@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,"")