Both are easy.
1. Assuming the "%" is anywhere within your text string simply replace the
cell reference in the lookup table with a formula which the result is either
the cell or "%", then 'lookup' that.
=VLOOKUP(IF(ISERROR(FIND("%",A2)),A2,"%"),$D$2:$E$ 23,2,FALSE)
then for any value with a '%' anywhere in the text the lookup will look for
"%" instead of A2. MAKE SURE YOU HAVE A "%" in your lookup table.

2. Instead of leaving the return value in your lookup table 'blank' - or a
'null' value (ie. nothing in the cell), put a 'space' in the cell. It will
now be a specific character - although it will look like an empty cell.



"Cal" wrote:

Thank you guys SO much for your help. I managed to get it to work..
almost.
I have 2 questions more :)

First one is, I have a bunch of different items that have a percent in
their name. Even though they have different names is there anyway to
make it so when it sees a percent sign (%) it codes it always the
same. Should I just do a % with a code in my Products range and then
change VLOOKUP to "true"?

Second question is, I have some items that are coded with a blank cell
because there are so many different kinds. The VLOOKUP puts a 0 in
instead of a blank cell. This is not a big deal but is there anyway
to make it put a blank cell instead?

THANK YOU so much for your help, I must be truelly a computer geek as
this breakthru has made my weekend! :) x5