Using VLOOKUP to match similar text?

[lrb]

I'm trying to use the VLOOKUP and need to know if it is possible to look up
text that "contains" the values in my table array, but is not an exact match.


For example ... my table array says "EE-A", but the cell I'm comparing it to
contains "EE-A, FlexMed ($25.00)". Is there a way to write the formula so
that it will recongnize the information before the comma (or some other type
of break) and will return the value assigned to the "EE-A" in my table array?
I don't know what it would be called or how to write it. It should be a
type of formula that if the cell "contains" but is not exactly equal to ...
any suggesetions?

 

[Ron Coderre]

Try this:
=VLOOKUP(Left(A1,FIND(",",A1)-1),YourTableOfData,YourValueToReturnCol,0)
That will take the EE-A from "EE-A, FlexMed ($25.00)" and find that value in
your table.

Does that help?

 

[lrb]

Ron,

I tried this but still get an error. My actual information doesn't have ""
around it. I don't know if that has something to do with the problem.?
Here's what the cell looks like: EE+Fam-A, FlexMed ($41.66) and here is what
I need it to find from the table array: EE+Fam-A $739.46

I need it to see that the EE+Fam-A, FlexMed ($41.66) corresponds to the
result of $739.46 even though there is a ,FlexMed ($41.66).

To say the least, I am a NOVICE at writing/using these type of functions.
I'd appreciate your advice.
Lisa

 

[Ron Coderre]

I think it will help if you post the relevant range references (eg:table
array =B1:L50, etc) and we'll see if we can help you build a formula that
works for you.

 

[lrb]

Ok. The table array is cells 0213.

 

[lrb]

I wasn't sure if you saw my reply ... table array = 0213. Thanks for your
help!

 

[Ron Coderre]

IF Cel A1 contains EE+Fam-A, FlexMed ($41.66)
AND Col O has data like EE+Fam-A
AND Col P has amounts
THEN this formula should return the amount associated with EE+Fam-A:
=VLOOKUP(Left(A1,FIND(",",A1)-1),0213,2,0)

Does it?

 

[lrb]

Ron,

Yes it does! Thank you. One last question. Is there a way to copy this
formula and paste to MANY cells below it? When I try to copy and paste it
doesn't pick all the cells in the array table.

Thanks again for your help! I definately couldn't have figured this out on
my own!

 

[Ron Coderre]

Here you go:
=VLOOKUP(Left(A1,FIND(",",A1)-1),$0$2:$P$13,2,0)
The $ signs lock in the range.

(I got sloppy on that one...sorry)

 

[lrb]

Ron you rock! I don't know how you know all this stuff, but you're awesome!
This is going to save me huge amounts of time PLUS it will make sure my
dollars are correct rather than relying on my brain and fingers to enter the
right info!

Thanks so much!

 

[kay]

hi expert,
how can i use vlook in looking up a similar or equal number of a look up
value from another table of array and returning the corresponding horizontal
value ?