What would be the most effective way to do this?

[Joan Pham]

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.

 

[Rick Rothstein]

Here is one way. First, put your Fruits in a column somewhere and put your
Vegetables in another column somewhere else. For your posted items, I used
F2:F5 for the fruits (with F1 as the header) and G2:G5 for the vegetables
(with G1 as the header) in this formula...

=IF(ISNUMBER(MATCH(A1,F$2:F$5,0)),"Fruit",IF(ISNUMBER(MATCH(A1,G$2:G$5,0)),"Vegetable",""))

If the contents of A1 is in the Fruit listing, the word Fruit is displayed;
if the contents of A1 is in the Vegetable listing, the word Vegetable is
displayed; and if the contents of A1 is in neither listing, nothing is
displayed (that is what the "" at the end of the formula does... you can
change this to a text string if you want to give feedback to the user). The
two lists do not need to be of the same size and the formula can be copied
down as needed.

 

[Driftwood]

Have an extra sheet - Sheet2- ( can be hidden) where you keep that list:


Column A: Column B
Toamto Vegetable
Apple Fruit
Celery Vegetable
Cherry Fruit
Banana Fruit
so in your sheet - Sheet1 - column B:
=VLOOKUP(A1,Sheet2!A1:B5000,2,FALSE)

 

[Teethless mama]

=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/122666560/3ac78e0b/ProductList.html

 

[Joan Pham]

Here is one way. First, put your Fruits in a column somewhere and put your
Vegetables in another column somewhere else. For your posted items, I used
F2:F5 for the fruits (with F1 as the header) and G2:G5 for the vegetables
(with G1 as the header) in this formula...

=IF(ISNUMBER(MATCH(A1,F$2:F$5,0)),"Fruit",IF(ISNUMBER(MATCH(A1,G$2:G$5,0)), "Vegetable",""))

If the contents of A1 is in the Fruit listing, the word Fruit is displayed;
if the contents of A1 is in the Vegetable listing, the word Vegetable is
displayed; and if the contents of A1 is in neither listing, nothing is
displayed (that is what the "" at the end of the formula does... you can
change this to a text string if you want to give feedback to the user). The
two lists do not need to be of the same size and the formula can be copied
down as needed.

I don't want a separate sheet. I want it built in to the function.

 

[Rick Rothstein]

I never suggested you use another sheet... just two unused columns both of
which can be on the same worksheet and, if desired, can be hidden so no one
sees them. As for putting the list into a formula... that probably would not
be able to be done given the 1024 character limit on the length of an XL2003
(or earlier) formula, although if you are using XL2007, then your list
should fit within its 8192 maximum character formula limit. There is, of
course, a VB solution if you want to go that route. Let us know which
version of Excel you are using and, if it is not XL2007, whether a VB
solution would be acceptable.

--
Rick (MVP - Excel)

Here is one way. First, put your Fruits in a column somewhere and put your
Vegetables in another column somewhere else. For your posted items, I used
F2:F5 for the fruits (with F1 as the header) and G2:G5 for the vegetables
(with G1 as the header) in this formula...

=IF(ISNUMBER(MATCH(A1,F$2:F$5,0)),"Fruit",IF(ISNUMBER(MATCH(A1,G$2:G$5,0)),
"Vegetable",""))

If the contents of A1 is in the Fruit listing, the word Fruit is
displayed;
if the contents of A1 is in the Vegetable listing, the word Vegetable is
displayed; and if the contents of A1 is in neither listing, nothing is
displayed (that is what the "" at the end of the formula does... you can
change this to a text string if you want to give feedback to the user).
The
two lists do not need to be of the same size and the formula can be copied
down as needed.

I don't want a separate sheet. I want it built in to the function.