Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP to match?
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 ? "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete all text that match | Excel Worksheet Functions | |||
How to match two different cells text, if the text is not in the . | Excel Worksheet Functions | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
Vlookup returns incorrect match | Excel Discussion (Misc queries) | |||
Vlookup, Index & Match | Excel Worksheet Functions |