Ask a new question

using wildcard in VLOOKUP table_array

I am plum googled out.  I have a worksheet where I need to find if values from one column exist in a range in another worksheet using VLOOKUP.  The table_array has some values that contain wildcards.  I cannot figure out how to get this to work.  

EX (sorry I couldn't get the table to post right)

lookup value table_array col 1 table_array col 2
ab1234 ab* ok
x456 cbrt123 ok
cbrt123 cbrt7000 ok
cbrt7000 mod ok
mod x* ok
ab7890
ab9876
x5677
x1234145

I need the result to show that 'ab1234', 'ab7890', 'ab9876', 'x5677' and 'x1234145' exist in the table_array because it is matching to "ab*" or 'x*'.  I found many examples where the wildcard is in the lookup value, but none where the wildcard is in the table_array.  Anyone know how to do this?

Answer
Answer

Hi,

Try this formula in cell B2 and copy down

=IFERROR(LOOKUP(2,1/SEARCH($F$2:$F$6,A2),$G$2:$G$6),"check")

table_array col1 and table_array col 2 is in F2:G6.

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Hi,

Maybe this can help.

Ashish Mathur

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 6, 2025 Views 1,482 Applies to: