Sorry for the vague title. Trying to get this done in a nice way, but feels more difficult than it looks. I have two tables - Strings and Data. Starting off with data in grey columns and I need to extract verified data to green (already filled as a manual "to-be" example).
Values in table Data should be looked for in Strings table, and extracted to columns Match1 and Match2. I surely don't want to over-complicate formulas, but the less helper columns, the better. I could use additional helper column and run wildcard-XLOOKUP such as (please see pink folumn F):
+ then add another XLOOKUP in column B to grab the E-column value through the pink helper column. However I'm not sure how to handle case such as row no 7, where there are more than 1 match to fetch.
Should also 2 helper columns be used?
Or is there a nicer, more tidy way to do this?
I'm running Office365, so newer Excel tools should be available.
Thanks a lot!
Values in table Data should be looked for in Strings table, and extracted to columns Match1 and Match2. I surely don't want to over-complicate formulas, but the less helper columns, the better. I could use additional helper column and run wildcard-XLOOKUP such as (please see pink folumn F):
Code:
=XLOOKUP("*"&E3&"*",Strings[String],Strings[String],"",2,1)+ then add another XLOOKUP in column B to grab the E-column value through the pink helper column. However I'm not sure how to handle case such as row no 7, where there are more than 1 match to fetch.
Should also 2 helper columns be used?
Or is there a nicer, more tidy way to do this?
I'm running Office365, so newer Excel tools should be available.
Thanks a lot!
