CODEWITHSUNDEEP
excelvlookupvba
Ashkan
Ashkan

Reputation: 31

Lookup a value from a cell with multiple values

I have an excel file containing 2 columns (Area Code) & (State). 

**Area Code**                                                          **State**
217, 224, 309, 312, 331, 618, 630, 708, 773, 779, 815, 847, 872          Illinois
219, 260, 317, 574, 765, 812                                             Indiana
319, 515, 563, 641, 712                                                  Iowa
316, 620, 785, 913                                                       Kansas
270, 502, 606, 859                                                       Kentucky

I want to use vlookup() for a given area code like "620" and get "Kansas". please note that all the values in a row are stored in one cell (i.e. "270, 502, 606, 859" are stored in one cell)

Upvotes: 0

Views: 4241

Answers (2)

A.S.H
A.S.H

Reputation: 29352

=VLookup("*620*", A2:B6, 2, false)

In VBA:

Function FindState(code as integer) as string
   FindState = Application.VLookup("*" & code & "*", mySheet.Range("A2:B6"), 2, false)
End sub

Upvotes: 2

Balinti
Balinti

Reputation: 1534

=VLOOKUP(INDEX(A:A,MATCH(TRUE,ISNUMBER(FIND(d2,A:A,1)),0)),A:B,2,0)

Assuming the number you want to find is in d2 and the data is stored in columns a and b.

I asked to find the first find without and error and return the row number. After that, I a lookup for it.

Use array formula ctrl+shift+enter

Upvotes: 0

Related Questions