Index-Match: String in cell to mappings

Question

I am trying to match strings to a text. I would like to return the index of these "mappings".

I tried: =INDEX($A$2:$A$1000;MATCH(F2;$B$2:$B$1000;1))

However, I wrong values:

In the mappings the values are all different as shown in the example!

Any suggestions what I am doing wrong?

Appreciate your reply! UPDATE

I used the below formulas in a google spreadsheet:

https://docs.google.com/spreadsheets/d/1qTrAcwIWuGFhF7w6Dm2-A_1km9eAyILnEzkac8GMkQ8/edit?usp=sharing

I do not get the mappings yet. Any suggestions what I am doing wrong?

Answer 1

MATCH(F2;$B$2:$B$1000;1) returns the position of the last cell in $B$2:$B$1000 which is greater than or equal to F2.

• 'This text contains Map Me1' is greater than all of the cells in $B$2:$B$1000 so you get the last one => 'Map10' when indexed in $A2:$A1000

• 'map me 2' is equal to map me 2 so you get the matching cell => 'map 2'

• There are no cells less than or equal to 'Lorem ipsum map me3' so you get #N/A

You can put wild cards in the text you are trying to match (F2) but not in the lookup range ($B$2:$B$1000) so to do an inexact match you would have to use an array formula with FIND or SEARCH.

One such formula (assuming that the lookup list does not contain blanks) is

=INDEX($A$2:$A$1000,MATCH(TRUE,ISNUMBER(SEARCH($B$2:INDEX($B$2:$B$1000,COUNTA($B$2:$B$1000)),F2)),0))

which must be entered using CtrlShiftEnter

I have included an entry which does not match any items in the list.

The whole formula can be wrapped in an IFERROR statement to avoid #N/A.

IFERROR(INDEX($A$2:$A$1000,MATCH(TRUE,ISNUMBER(SEARCH($B$2:INDEX($B$2:$B$1000,COUNTA($B$2:$B$1000)),F2)),0)),"")

(please replace , with ; if this is appropriate to your locale).

This is a little shorter and is non-array formula but returns the last match if there are multiple matches.

=IFERROR(LOOKUP(9E+307,SEARCH($B$2:INDEX($B$2:$B$1000,COUNTA($B$2:$B$1000)),F2),$A$2:$A$1000),"")

Answer 2

Barry is the guru on formulas but you could try this array formula

In G1 enter =IF(LEN(F2)>0,INDEX($A$2:$A$10,MIN(IF(NOT(ISERR(FIND(LOWER($B$2:$B$10),LOWER(F2)))),ROW($B$2:$B$10)-1,MAX(ROW($B$2:$B$10)+1)))),"no data")

using shiftctrlenter together