VBA Lookup

[Closed]
Report
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
-
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
-
Hello,

I currently have a macro built that takes a value from a specific cell and looks against a lookup table in another work sheet. If the value is found, then it returns a value back with the data in the cell next to it.

This is the current code that I have...
ActiveCell.FormulaR1C1 = "=VLOOKUP(TRIM(RC[2]),Lookup!R1C1:R9000C2,2,0)"

The problem I am having is now I need a way to return the value if part of the data is found in that cell. So here is an example.

Sheet 1
A1 is where my results will be populated from the lookup table
C1 is where the lookup is comparing against

Lookup
A1 - A9000 is where the lookup values that need to be compared are.
B1 - B9000 is the data to populate back into Sheet 1, cell A1 if a lookup is found.

So Sheet 1, C1 = "123PRE456", Lookup, A1 = "*PRE*", Lookup, B1 = "PRE ITEM"

I am looking for how to write the code to see *PRE* as a valid value in the lookup. Currently I just get an NA when it is ran.

Thank you,
Matt

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
have you tried

ActiveCell.FormulaR1C1 = "=VLOOKUP(""*"" & TRIM(RC[2]) & ""*"",Lookup!R1C1:R9000C2,2,0)"
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
2
That helped, but I need it to find it both ways. Sometimes searching for just part of it and sometime doing a full match. Is that even possible?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It is a formula, how can it behave in two different ways Matt ? If you mean that first find exact match, if exact match is not found use the wild card match, you can use IF

=IF(ISERROR(VLOOKUP(exact lookup cond)), vlookup(wild card look up), vlookup(exact look up))
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
2
I think I am asking this incorrectly. I don't think I need to change the coding, I think I need to know if there is a way to do a partial seach from the lookup table itself. For example.

Sheet 1
A1 = 123PRE345
A2 = 234PRE567
A3 = 123ABC456

Sheet 2 (Lookup Table)
Column A Column B
123ABC456 ABC KIT
*PRE* PRE KIT

So for cell A1 and A2 it would return a value of PRE KIT, and A3 it would return a value of ABC KIT.

Does anybody know how to do a partial search like this.

Thank you,
Matt
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Matt what version of excel you are using ?
Posts
16
Registration date
Tuesday March 23, 2010
Status
Member
Last seen
March 29, 2013
2
2007