Find value in range of strings and return row number

Question

Probably a easy question, but I've been stuck on this for days and can't find anyone with a similar problem, except when the values are in different cells.

A:Country   B:Zipcode
Denmark     3700 3701 3705 3708 3805 3902
France      17191 17193 17198
Germany     15760 15878 15985

For example:

Denmark & 3700 - return row 2. 
Denmark & 17191 - no match. 
France & 17191 - return row 3.

I have tried several solutions with FIND's, SUMIF's, SOMPRODUCT etc., but none is able to retrieve the row when all the zipcodes are in one cell.

I am able to retrieve whether the zipcodes exists in the entire column B, but not in which cell it finds a match.

What's the maximum number of zip codes you'd ever have in a cell? — Bathsheba
– Bathsheba, Commented Apr 19, 2018 at 10:38
Sounds like a VBA function to me then, based on the Split function. — Bathsheba
– Bathsheba, Commented Apr 19, 2018 at 11:17

Developer Guy · Accepted Answer · 2018-04-19 12:38:53Z

1

or

=IFERROR(MATCH(1,ISNUMBER(SEARCH(B2,$A$1:$A$4,1))*ISNUMBER(SEARCH(C2,$A$1:$A$4,1)),0),"No match")

Entered as an array formula with Ctrl + Shift + Enter

edited Apr 19, 2018 at 12:38

Developer Guy

2,4946 gold badges23 silver badges39 bronze badges

answered Apr 19, 2018 at 10:55

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Danny Over a year ago

Thanks for the response, I tested this method and it works! However my data is separated in 2 different columns, so the method provided by @sktneer is more suiteable for me.

QHarr Over a year ago

If it is two columns simply change range for the second search e.g. to B1:B4 where ever you have to pick the other values up from. That should work.

Danny Over a year ago

Works perfectly. The other solution by @sktneer had a weird issue where cells with a large textstring weren't working, but this one works flawlessly. Thanks!

Danny Over a year ago

One small issue though: it also return true if only the zipcode is found as partial zipcode. For example: 7191 in the above zipcodes for France will return true, eventhough the exact match should be 17191. I will try to find a work-around for this.

QHarr Over a year ago

You could add a Len test to ensure zipcode is same length

|

Subodh Tiwari sktneer · Accepted Answer · 2018-04-19 10:51:32Z

1

If the data is setup as per the image below, you may try something like this...

In F2

=IFERROR(INDEX(ROW($A$2:$A$4),MATCH(D2&"*"&E2&"*",INDEX($A$2:$A$4&$B$2:$B$4,),0)),"No Match")

answered Apr 19, 2018 at 10:51

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

3 Comments

Danny Over a year ago

Although this is the correction solution and I can get it working with the dataset I provided, for some reason it does not work for large data sets (>100's of zipcodes in the cell), but can't figure out why.

Subodh Tiwari sktneer Over a year ago

It should work with any data set provided the layout is the same. :)

Danny Over a year ago

For some reason, when the length of the string in the zipcode column is longer than 245 characters, the formula stops working. Is this the same for you?

Collectives™ on Stack Overflow

Find value in range of strings and return row number

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related