Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

MATCH WITH WILD CARD

0

I am trying to fill in the price column on the attached worksheet.

any help will be appreciated

Thanks  Carroll

Answer
Discuss

Answers

0
Selected Answer

Try this dude:

=INDEX(B$2:B$4, AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&A$2:A$4&"*", D2, 0)), 1))

It's a bit funky and uses some new features in later versions of Excel, but you should be good with Excel 2013.

Make $1:$3 have as many rows as your lookup data will have and don't worry how this part appears on the worksheet.

I tested this and it seems to work fine.

However, you will have to be careful if there are multiple values from the lookup list in a single cell, such as "it's a dog and not a pig!"

Discuss

Discussion

Thanks much for your help.
Carroll
carroll (rep: 16) Apr 1, '19 at 8:56 pm
I'm glad it worked! This was actually a pretty interesting scenario that I hadn't thought about before and I think I'll make a tutorial out of it :)
don (rep: 1989) Apr 2, '19 at 2:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login