CODEWITHSUNDEEP
vbaexcellookup
Sri Katte
Sri Katte

Reputation: 67

Excel VBA kinda Reverse Lookup - any better options?

Setup

I have two sheets with one sheet containing the textual data and another containing the look up data.

enter image description here

Problem

I need to determine if a line contains a matching countryname and extract its corresponding id from sheet 2. This is reverse of VLOOKUP or Match function where I am looking for sheet 1 data in sheet 2.

Any easy way to do this other than looping through the entire set of sheet 2 data for each of the lines in sheet 1 data?

Upvotes: 1

Views: 954

Answers (2)

Kevin
Kevin

Reputation: 2631

How about:

=LOOKUP(9.9E+300,SEARCH(Sheet2!$B$1:$B$2,A25),Sheet2!$A$1:$A$25)

Change $25 to your exact range, or use named ranges. This will return the ID if found, #N/A if not found, and if there is more than one match you will only get the last match.

EDIT: If you want the first match you could use this formula:

=INDEX(Sheet2!$A$1:$A$25,MATCH(1=1,INDEX(ISNUMBER(SEARCH(Sheet2!$B$1:$B$25,A1)),0,0),),1)

EDIT II: How this works

The SEARCHfunction in both examples says find the starting position of any of the words in the range (array) Sheet2!$B$1:$B$25 for cell A1

In the first example LOOKUP will then look for a number (the row number) less than 9.9E+300 and return the value from the row in the result vector (this is why it returns the last matched result).

In the second example MATCH(1=1,INDEX(ISNUMBER(SEARCH(Sheet2!$B$1:$B$25,A1)),0,0) will determine the row number by finding the first true (1=1) in the array produced by the INDEX function. Then the outer INDEX function returns the adjacent column (the ID).

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

You can do this with a worksheet formula.

I chose to use a Table and structured references, but you can use other methods:

On sheet1:

B2:    =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Countries[CountryName],A2)),Countries[ID]),"")

or

B2:  =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!$B$2:$B$258,A2)),Sheet2!$A$2:$A$258),"")

will return the ID or a blank.

This formula is normally entered. And LOOKUP will ignore the div/0 errors, matching the 1/True (=1) and returning the value in the corresponding position from result_vector

Upvotes: 0

Related Questions