=IF(COUNTIF(A2:E6,A9),INDEX(A1:E1,MAX(INDEX((A2:E6 =A9)*COLUMN(A2:E6),))),"not
on the list")

For more detail, go to this link

http://www.4shared.com/file/12266656...oductList.html


"Joan Pham" wrote:

Say I want to be able to when the cell says one thing have the
function say something else.

Sorry, that is a very bad way of explaining it. Perhaps this way would
be better.

Say that I have a list of fruits and vegetables column a will be
where I would type in the fruits and vegetables. column b would be
where the function will tell me if it's a fruit or a vegetable. In the
function it is going to have to list each fruit and vegetable I am to
encounter.

For simplicity sake lets limit the universe of fruits and vegetables I
will encounter to eight.

Fruits:

Apple
Banana
Orange
Grapes

Vegetables:

Radish
Lettuce
Carrot
Potato

So I would type in say in a1 Apple b1 would be =fruitorveg(a1) and
would show "Fruit." If I changed a1 to Radish b1 would show Vegetable.
And so on.

Again this is simplifying the concept here. What I really need to do
is in the formula list about two hundred of one thing and have it
identify itself as being part of something. But I feel if I have the
general concept down then it is just a matter of adding on lines.

I hope the example explains it well.

I want to do it in a function rather than like have a excel datasheet
that compares the two pieces of data because I want to have it
available when I need to use it without having to bring up another
worksheet.