Complex Vlookup/ VBA function or Macro Needed For "if contains text then vlookup"

Hi

I have a problem, http://goo.gl/i82eA this is sample data that I have with the required output. Currently I have a user defined function that is manually using many if statements to do the job but I want to be able to do something like a vlookup if it finds a certain color in a colum and return the colormap coresponding to it.

Or use like a filter function like filter all cells that contain Blue and give the destination cell with blue, and then run next filter with next value in an table of colors.

Color ColorMap Text Required Output
blue blue Deep Blue Shoe Blue (if Text contains blue return blue)
red red Deep red Shoe red (if Text contains red return red) etc
tan brown Tan Shoe brown
navy blue Navy Emp Shoe blue
jade green Jade Shoe green
plum red Plum Red Shoe multicoloured (if Text contains more than 1 color return multicolored)

So input should be like 2 columns for the data to be looked up 1 column for searching and the 1 column is the destination column if its a function

function_name(lookup_text,lookup_table,destination)


Thanks in advance








ps: here is the code Im using now

Please Login or Register  to view this content.

post a workbook not a link

alfykunable,

Please see the attached workbook. The example assumes that you have entered all of the lookup values (the color to search for) and output text (corresponding colormap) from your current code into a table somewhere in your workbook. When you use the function to specify a search string, like "Deep Blue Shoe", it will look in this table for your lookup values and output text. In this example, I placed this table on a worksheet called setup. I then created a dynamic named range to refer to this table, using the following formula:

Please Login or Register  to view this content.

The OFFSET formula I used to refer the search range uses the COUNTA formula in the [height] parameter to return a table height that is equal to the number of non-blank cells in the specified range (in this case, column A). By using this method, I can add lookup values and colormaps by simply adding a row to the bottom of my table. COUNTA will recognize that another non-blank cell as been added to Column A and tell the OFFSET formula to "grow" one row bigger.

Please note that my table contains headers, which I don't want to be included in the search range when the function fires. That is why the OFFSET formula begins at cell $A$2...and which is also why there is a "-1" at the end of my COUNTA formula. If you do not want to include headers in you table, the named range formula should look like this:

Please Login or Register  to view this content.

Here's a very quick explanation of how the function works: when you enter the COLORMAP function into a cell and identify the search string, the function will loop through the list of lookup values from the table you set up, looking for a match within your search string. If it finds one, it will return the colormap that corresponds with that lookup value. If it finds more than one match, it will return Multicolor.

Place the following code into any standard code module in the attached workbook, as the example workbook was uploaded without it.

Please Login or Register  to view this content.

I briefly tested this, and everything seems to be in order. Hopefully it works for you as well. If not, let me know.

Hi Ryan,

Thanks for the file,

works like a charm


just 1 pointer, how can i change it to be case insensitive?

Because In the Lookuptable I put
Plum Red - Red

but when i colormap(Plum Red) it gave Multicolored and not red why?

alfykunable,

It's not the case. If you add Plum Red - Red to your lookup table, it should work.