How to use wildcards "*" in a formulate like vlookup

gveilleux

I am looking for a formula like vlookup that can use wildcards to search text within a cell.

Excel example

=VLOOKUP(D1&"*",A1:B9,2,FALSE)

See attached

gveilleux

I was able to figure it out. But will take suggestions if they are out there.

Genevieve P.

Hi @gveilleux

You can use the CONTAINS function to check within a column to see if any cell contains a partial bit of text.

However instead of a VLOOKUP I would maybe suggest a JOIN(COLLECT, in case you have more than one match.

E.g:

=JOIN(COLLECT({Column to Return}, {Column with Criteria}, CONTAINS("Text", @cell)), ",")

Resources that may be useful: JOIN Function / COLLECT Function / CONTAINS Function / Create Efficient Formulas with @cell and @row / Cross-sheet formulas

Cheers,

Genevieve

gveilleux

I was able to figure it out. But will take suggestions if they are out there.