CODEWITHSUNDEEP
excelvlookup
Alex
Alex

Reputation: 77

How to lookup data with a partial match on search key

I have two spreadsheets. The first has a column with employee job IDs with commas seperating them (e.x: 123DB, 345CX, 031DB). The second spreadsheet has the job IDs and the description. For instance, 123D is Financial Advisor, 345C is Manager, etc... It is not an exact match because some of the other numbers and letters represent skill and other things.

So basically, I want to search the second spreadsheet for the job ID and print the description in a new column on that first spreadsheet (but it's not an exact match; like 123BD=123XB=123AP). I'm not sure if VLOOKUP is what I want. Please and thank you!

Also, there are tens of thousands of rows to brute force isn't possible. I plan to get more descriptive as I go on so if 23D is finance, 23D04 is a financial regulator or something.

Desired Result

Upvotes: 0

Views: 322

Answers (1)

Michael
Michael

Reputation: 4883

Yeah you can't comfortably deal with that many IDs in a single formula. Below is the formula for finding the Description for a single ID # in cell A2.

This formula assumes the description lookup range has been converted to a table that has been named "Jobs".

Also, this is an array formula that needs to be entered using CTRL+SHIFT+ENTER

=INDEX(Jobs[Description],MATCH(TRUE,ISNUMBER(SEARCH(Jobs[Job Code],A2)),0))

Considering it is searching for this in the middle of a string that seems to contain various other random numbers and letters, you may get some false matches. These are hard to avoid without there being a more fixed structure to the IDs.

Upvotes: 1

Related Questions