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 eac...
=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.
