1

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:

enter image description here

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?

Community
  • 1
  • 1
Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • Your formula should be =ArrayFormula(INDEX($A$2:$A$1000,MATCH(TRUE,ISNUMBER(SEARCH($B$2:INDEX($B$2:$B$1000,COUNTA($B$2:$B$1000)),H2)),0))) i.e. change SEARCH($B$4... to SEARCH($B$2... (I have already changed the first 2) – Tom Sharpe Sep 19 '16 at 17:01

2 Answers2

2

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

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Shorter and simpler - therefore better - than mine. – brettdj Sep 19 '16 at 10:03
  • Thanks! But after doing a bit more research the LOOKUP trick is even shorter =LOOKUP(9E+307,SEARCH($B$2:INDEX($B$2:$B$1000,COUNTA($B$2:$B$1000)),F2),A$2:A$1000) and non-array formula. – Tom Sharpe Sep 19 '16 at 10:57
  • @TomSharpe Thank you very much for your reply! I tried to use your formula in a google spreadsheet, but I do not get the exact results. The mappings do not work in it. Any suggestions? – Carol.Kar Sep 19 '16 at 13:59
  • @MrQuad These do work in Google Sheets but you have to wrap them in the ArrayFormula(...) function instead of entering them with Ctrl-Shift-Enter. Apologies, using Ctrl-Shift-Enter does work in Google Sheets and puts ArrayFormula round the original formula instead of curly brackets {}. – Tom Sharpe Sep 19 '16 at 15:46
  • What does the `9E+307` in the lookup mean? – Carol.Kar Sep 19 '16 at 18:34
  • Near to the largest number you can have in Excel - according to the documentation, the largest possible number is 9.99999999999999E+307 i.e. 9.99999999999999 multiplied by 10*10*10... 307 times or 999999999999999 followed by 293 zeroes. So it will match the last one (without an error and in ascending order) of any list of numbers that you are likely to use. – Tom Sharpe Sep 19 '16 at 19:16
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 together

enter image description here

brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Also works but I didn't quite get the MAX part of it? – Tom Sharpe Sep 19 '16 at 11:28
  • To knock out the non-matches. Remembering now why I stopped playing with formulae when I saw that lookup..... – brettdj Sep 19 '16 at 11:49
  • Thx you very much for your reply! I tried to use your formula in a google spreadsheet to use it to map my income/expenses. Any suggestions what I am doing wrong? Here is the link to an example: https://docs.google.com/spreadsheets/d/1qTrAcwIWuGFhF7w6Dm2-A_1km9eAyILnEzkac8GMkQ8/edit?usp=sharing – Carol.Kar Sep 19 '16 at 14:00
  • You need to change $B$4 to $B$2 in your formula – Tom Sharpe Sep 19 '16 at 16:08