I have the following sample table:
I am looking for a Google Sheets formula that would match the Name column to "Ben Herbs" and return its corresponding value of 10 into a cell. Please note I don't want to match exactly to "Ben Herbs 0" because the number is dynamic and will change over time.
How do I do this in Google Sheets formula?
You may try the xlookup()
wildcard search:
=xlookup(<cell_containing_BEN HERBS>&"*",<NAME_column>,<VALUE_column>,,2)
example: =xlookup(D2&"*",A:A,B:B,,2)