Excel MATCH character limit

Question

I use the following formula =INDEX(Dict!A:A,MATCH(A2,Dict!A:A,0),1) but MATCH only works with text below 256 characters. Any way to overcome this limitiation?

Answer 1

To accommodate partial matches use SEARCH like this:

=INDEX(Dict!A:A,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,Dict!A:A)),0),0))

That will work to return a value > 256 characters but A2 can't be > 256 characters

Answer 2

There is another way to do this, if you don't mind the messiness of a helper column, and your original formula is not being repeated in subsequent rows (i.e. matching cells A3, A4, A5...). The helper column can of course be hidden to keep DICT looking pretty.

1. Insert (or use) a column (say B:B) next to A:A in DICT!, and populate it with a simple formula "=A1=SHEET1!A$2" (SHEET1 being the name of your source/original sheet), which will populate the column with TRUE and FALSE values, indicating which rows (if any) in DICT match to A2.

2. The match syntax then changes from "MATCH(A2,Dict!A:A,0)" to "MATCH(TRUE,Dict!B:B,0)".

Note: I know this works in principle as I have just done it, but if I added a typo in retrofitting it to the example provided, apologies, however the principle should be easy to follow.

Answer 3

You can use the LDMP look-up method, taking advantage of the concat formula available since the Excel 2016.

={CONCAT(IF(Value=A:A;B:B;"")}

Note that it is a matrix formula so you must enter it CTRL + SHIFT + ENTER. Additionally the formula returns not only the first value but all the matching values.