Using VLOOKUP to match similar text?

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?

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?
--
Regards,
Ron


"lrb" wrote:

> 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,

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" wrote:

> 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?
> --
> Regards,
> Ron
>
>
> "lrb" wrote:
>
> > 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?

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.
--
Regards,
Ron

Ok. The table array is cells 02:P13.

"Ron Coderre" wrote:

> 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.
> --
> Regards,
> Ron

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

"Ron Coderre" wrote:

> 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.
> --
> Regards,
> Ron

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),02:P13,2,0)

Does it?
--
Regards,
Ron

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" wrote:

> 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),02:P13,2,0)
>
> Does it?
> --
> Regards,
> Ron
>

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)

--
Regards,
Ron


"lrb" wrote:

> 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" wrote:
>
> > 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),02:P13,2,0)
> >
> > Does it?
> > --
> > Regards,
> > Ron
> >

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!

"Ron Coderre" wrote:

> 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)
>
> --
> Regards,
> Ron
>
>
> "lrb" wrote:
>
> > 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" wrote:
> >
> > > 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),02:P13,2,0)
> > >
> > > Does it?
> > > --
> > > Regards,
> > > Ron
> > >