CODEWITHSUNDEEP
excel
Maykid
Maykid

Reputation: 517

How do I cross reference an ID within a text to the category its associated with on another sheet?

So the problem I'm currently having is I have an ID within a string, and I'm trying to grab that ID to be able to pull in the Category on that same sheet. Here's what it looks like:

Sheet1

The one column I'm currently focused on the is the Placement column as they contain the ID's. As you can see the ID's vary in how many numbers are in each and some have different lengths instead of being the same.

Sheet2

This is the lookup sheet (Sheet2) that I have that corresponds with the ID to Category. What I want the result too look like is:

Results

After speaking with a few others, I thought about the VLOOKUP(MID Formula but for this I'm not sure if it will work properly. I also thought of doing a FIND/SEARCH with INDEX MATCH function but can't quite grasp it as all throws an error when I try to get a formula to work.

If any further explanation is needed please let me know and I can try to explain further.

Thank you in advance!

-Maykid

Upvotes: 0

Views: 102

Answers (2)

Erjon
Erjon

Reputation: 930

If there are no other patterns of column b, so if the maximum of symbol "_" before the id is only 2 then you can use this formula:

=INDEX(Sheet2!B:B,MATCH(IFERROR(NUMBERVALUE(MID(B1,FIND("_",B1)+1,FIND("_",B1,FIND("_",B1)+1)-FIND("_",B1)-1)),NUMBERVALUE(MID(B1,FIND("_", B1,FIND("_",B1)+1)+1,FIND("_", B1,FIND("_", B1,FIND("_",B1)+1)+1)-FIND("_", B1,FIND("_",B1)+1)-1))),Sheet2!A:A,0))

Just copy the formula and paste it in your sheet, I just tested and works fine, the problem is if there will be more than two "_" before the id for example sometext_SomeOtherText_OtherText_123_Text in this case it will throw an error

Upvotes: 2

Flared Magwell
Flared Magwell

Reputation: 1

This formula will split out the ID from your placement column. 

=MID(B1, SEARCH("_",B1) + 1, SEARCH("_",B1,SEARCH("_",B1)+1) - SEARCH("_",B1) - 1)

Upvotes: 0

Related Questions