Representation

[mark1]

I need to make one value in Excel represent another value
and vice versa. In other words, I need to be able to
enter the letter "B" in a cell and make Excel understand
that B can represent B, Business, Busi or Bus, so that I
can do a vlookup or sumproduct formula using B as my
criteria. The formula would then return all values that
have a B, Business, Busi or Bus beside them. I tried
Insert->Name, but either I was doing it wrong or...

PS - I need to make it work the other way around too. So,
that I can enter "Bus" and excel will know that it can
mean B, Busi, Bus or Business.

Any help is greatly appreciated!! Thanks!!

 

[Frank Kabel]

Hi for using this with SUMPRODUCT you may try the following:
1. enter these values in a range (lets say on sheet1!A1:A4) and define
a name for them 'Insert - Name Define'. Let say you call it 'sum_crit'

2. Now if you want to count the occurences of these lookup values in a
range you may try the following array formula 8entered with
CTRL+SHIFT+ENTER)
=SUMPRODUCT(--(A1:A1000=TRANSPOSE(sum_crit)))