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: 14) 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: 1616) 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