vlookup IF "contains"

[Pizzio]

Searching for a formula similar to a vlookup. However, instead of an absolute value it would be if CONTAINS X reference

Example: using Stacy as my lookup value

1847Stacy56 would be match as Stacy is contained in my lookup array

 

[6StringJazzer]

=INDEX(B:B,MATCH("*Stacy*",A:A,0))

Assuming you are looking at data in column A and want to return the corresponding row of column B.

 

[Pizzio]

=INDEX(B:B,MATCH("*Stacy*",A:A,0))

Assuming you are looking at data in column A and want to return the corresponding row of column B.

What if instead of Stacy I reference cell C1? When I made the change if failed. BUT your original formula works when applying Stacy

 

[6StringJazzer]

What if instead of Stacy I reference cell C1? When I made the change if failed. BUT your original formula works when applying Stacy

Always ask the question you actually want to ask. You'll get the right answer the first time.

=INDEX(B:B,MATCH("*" & C1 & "*",A:A,0))