Index and Match

[Tordah]

Good Day,

I am trying to figure out why my formula is not working. as you can see in the attached, I have Column C with names and D with their "Abbreviations" of course nothing official but When I type Pear into the searching for field, the Result shows up as NEC, indicating Nectarine. Why? As well, how would I search for Grapes instead of Grape?

The later is my bigger issue atm. I have a real sheet that keeps giving me line 142 instead of 143 where it should be. I can't search exact, but if I absolutely MUST, I will create a dropdown but at 178 items, that is excessive for a dropdown.

[CAntosh]

Your MATCH function would benefit from a third term to specify an exact match:

=INDEX(D1:D6,MATCH(D8,C1:C6,0))

[CAntosh]

I should finish reading the entire question next time...

[Tordah]

NP but thanks for the quick reply anyways.

[CAntosh]

Try searching exact with a wildcard:

=INDEX(D1:D6,MATCH(D8&"*",C1:C6,0))

Will make the match for grape in D8 even if C3 says grapes. Using a non-exact match is really only useful for numbers that are in order or text in alphabetical order.

[Tordah]

All my text will be in order, I just have a long list of unique names to get through, but it's just not matching correctly, I will try this though and get back to you.

[Tordah]

It didn't work in my real sheet, I put something like grap and it failed the search.

[CAntosh]

Failed as in error? Or failed as in it returned "grapefruit"? It should return the first match that starts with the search term ("grap")

[CAntosh]

Here's a more robust, if slightly ridiculous-looking, version:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(D1:D6,MATCH(D8,C1:C6,0)),INDEX(D1:D6,MATCH(D8&"s",C1:C6,0))),INDEX(D1:D6,MATCH(LEFT(D8,LEN(D8)-1),C1:C6,0))),INDEX(D1:D6,MATCH(SUBSTITUTE(D8,"ies","y"),C1:C6,0))),INDEX(D1:D6,MATCH(LEFT(D8,LEN(D8)-1)&"ies",C1:C6,0))),INDEX(D1:D6,MATCH(D8&"*",C1:C6,0)))

It first looks for a perfect match. Failing that, it adds an "s" to the search term and tries again. Failing that, it subtracts the final letter from the search term and tries again (in case the search term is plural but the list term isn't). Failing that, it replaces "ies" with "y" and searches. Failing that, it does the reverse, replacing the last letter with "ies". Finally, it does the general "starts with..." search that I proposed earlier. Not quite perfect, but it should be close.

[Haseeb Avarakkan]

Hello Tordah,

Try this version:

=INDEX(D:D,LOOKUP(2^20,MATCH(MID(D8,1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(D8))))&"*",C:C,0)))

[Tordah]

Hello Tordah,

Try this version:

=INDEX(D:D,LOOKUP(2^20,MATCH(MID(D8,1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(D8))))&"*",C:C,0)))

I do not understand the index(A:A,1):index(A:A, portion is for, why A:A? is this a dump column?

[Tordah]

Haseeb,

Thank you for that, I did get it to works mostly, but I can edit the couple that are just not working for me. At least it is about 5 compared to the 178 from before.

Again, thank you.