Forum Discussion
How to look up a partial string using either Xlookup or Power Query
Hi,
I'm wondering if it's possible to use Xlookup or Power Query to do a partial match from a table of possible matches.
For example: In the image below I'm trying to lookup the prices on each item from the second table how do I look up the price of Bike and Car (assuming there's no pattern in the lookup text so the item we're looking for could appear anywhere in the string).
So, a: lookup a partial match and b: look up the price from the table for that match.
I'm trying to avoid using nested IFs or using a static if statement or cleaning the data first.
I've tried bringing in table two as a fuzzy match in Power Query but the matching isn't reliable enough (in the actual data that I'm using) and it gives some spurious results.
=LOOKUP(9^9,SEARCH(Tabelle2[Item],[@Ref]),Tabelle2[Price])- Thanks, but I can't seem to het that to work.
What does '9^9' do?- It is just a big number. Bigger the maximum text length in your data.
The formula makes use of some properties of the LOOKUP() function.
If it finds no match - 9^9 is always bigger the number returned by SEARCH() - it will look for the next number from below. And LOOKUP() ignores errors.
