Previous Topic Next Topic

VBA Lookup

Closed
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 - Mar 21, 2013 at 05:16 PM
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 - Mar 29, 2013 at 07:04 PM
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

Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 23, 2013 at 07:46 AM
have you tried

ActiveCell.FormulaR1C1 = "=VLOOKUP(""*"" & TRIM(RC[2]) & ""*"",Lookup!R1C1:R9000C2,2,0)"
0
Answer 2 / 2
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 2
Mar 25, 2013 at 09:57 PM
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 26, 2013 at 07:13 AM
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))
0
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 2
Mar 27, 2013 at 10:52 PM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 29, 2013 at 09:00 AM
Matt what version of excel you are using ?
0
froggy6703 Posts 16 Registration date Tuesday March 23, 2010 Status Member Last seen March 29, 2013 2
Mar 29, 2013 at 07:04 PM
2007
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses