Hello,

I have a list of data in C, and would like to create a column in D that
organizes that data into a specific category. For example, given:

Fruit apple apple
orange orange
banana lettuce
kiwi banana
tomato kiwi
Veg lettuce potato
potato orange
banana
kiwi
tomato
apple
orange

I want to create a new column in D that returns "fruit" or "veg" -
whichever's appropriate for that row. The problem is that I have a lot of
items and a lot of categories. I can't get an IF or LOOKUP function to work.
I can do VLOOKUP, but would rather not reformat my data.

Any thoughts? Thanks!

Put this in D1:

=IF(ISNA(MATCH(C1,B:B,0)),"",IF(INDEX(A:A,MATCH(C1 ,B:B,
0))="",LOOKUP("zzz",INDIRECT("A1:A"&MATCH(C1,B:B,
0))),INDEX(A:A,MATCH(C1,B:B,0))))

and copy down as far as you need. It will cope with column C cells
being empty and with cells in C containing fruit or vegetables which
are not in column B (eg Plum) - both return blanks.

Hope this helps.

Pete

On Apr 21, 10:43*pm, Demosthenes
wrote:
Hello,

I have a list of data in C, and would like to create a column in D that
organizes that data into a specific category. For example, given:

Fruit * * *apple * * * * apple
* * * * * *orange * * * orange
* * * * * *banana * * * * * *lettuce
* * * * * *kiwi * * *banana
* * * * * *tomato * * * * * *kiwi
Veg * *lettuce * * * *potato
* * * * * *potato * * * * * * orange
* * * * * * * * * * * * * * *banana
* * * * * * * * * * * * * * * *kiwi
* * * * * * * * * * * * * * * *tomato
* * * * * * * * * * * * * * * *apple
* * * * * * * * * * * * * * * *orange

I want to create a new column in D that returns "fruit" or "veg" -
whichever's appropriate for that row. The problem is that I have a lot of
items and a lot of categories. I can't get an IF or LOOKUP function to work.
I can do VLOOKUP, but would rather not reformat my data.

Any thoughts? Thanks!

Think you could simplify the categorization task dramatically via
pre-populating fully cols A and B (fill-it down), viz make it:

Fruit apple
Fruit orange
Fruit banana
Fruit kiwi
Fruit tomato
Veg lettuce
Veg potato

Then simply place in D2, copied down: =INDEX(A:A,MATCH(C2,B:B,0))
should accomplish the task of categorizing all the data in col C in seconds
Above of any worth? hit YES below
--
Max
Singapore
---
"Demosthenes" wrote:
I have a list of data in C, and would like to create a column in D that
organizes that data into a specific category. For example, given:

Fruit apple apple
orange orange
banana lettuce
kiwi banana
tomato kiwi
Veg lettuce potato
potato orange
banana
kiwi
tomato
apple
orange

I want to create a new column in D that returns "fruit" or "veg" -
whichever's appropriate for that row. The problem is that I have a lot of
items and a lot of categories. I can't get an IF or LOOKUP function to work.
I can do VLOOKUP, but would rather not reformat my data.

Any thoughts? Thanks!

Pete,

Thanks! That does what I wanted.

Max,

Thanks! That works, but I was hoping to not reformat it like that.

"Max" wrote:

Think you could simplify the categorization task dramatically via
pre-populating fully cols A and B (fill-it down), viz make it:

Fruit apple
Fruit orange
Fruit banana
Fruit kiwi
Fruit tomato
Veg lettuce
Veg potato

Then simply place in D2, copied down: =INDEX(A:A,MATCH(C2,B:B,0))
should accomplish the task of categorizing all the data in col C in seconds
Above of any worth? hit YES below
--
Max
Singapore
---
"Demosthenes" wrote:
I have a list of data in C, and would like to create a column in D that
organizes that data into a specific category. For example, given:

Fruit apple apple
orange orange
banana lettuce
kiwi banana
tomato kiwi
Veg lettuce potato
potato orange
banana
kiwi
tomato
apple
orange

I want to create a new column in D that returns "fruit" or "veg" -
whichever's appropriate for that row. The problem is that I have a lot of
items and a lot of categories. I can't get an IF or LOOKUP function to work.
I can do VLOOKUP, but would rather not reformat my data.

Any thoughts? Thanks!

You're welcome - thanks for feeding back.

Pete

On Apr 22, 4:55*pm, Demosthenes
wrote:
Pete,

Thanks! That does what I wanted.

Max,

Thanks! That works, but I was hoping to not reformat it like that.



"Max" wrote:
Think you could simplify the categorization task dramatically via
pre-populating fully cols A and B (fill-it down), viz make it:

Fruit * * *apple
Fruit * * *orange
Fruit * * *banana
Fruit * * *kiwi
Fruit * * *tomato
Veg * * * *lettuce
Veg * * * *potato

Then simply place in D2, copied down: =INDEX(A:A,MATCH(C2,B:B,0))
should accomplish the task of categorizing all the data in col C in seconds
Above of any worth? hit YES below
--
Max
Singapore
---
"Demosthenes" wrote:
I have a list of data in C, and would like to create a column in D that
organizes that data into a specific category. For example, given:

Fruit * *apple * * * * apple
* * * * *orange * * * orange
* * * * *banana * * * * * *lettuce
* * * * *kiwi * * *banana
* * * * *tomato * * * * * *kiwi
Veg * *lettuce * * * * * * *potato
* * * * * *potato * * * * * orange
* * * * * * * * * * * * * * *banana
* * * * * * * * * * * * * * * *kiwi
* * * * * * * * * * * * * * * *tomato
* * * * * * * * * * * * * * * *apple
* * * * * * * * * * * * * * * *orange

I want to create a new column in D that returns "fruit" or "veg" -
whichever's appropriate for that row. The problem is that I have a lot of
items and a lot of categories. I can't get an IF or LOOKUP function to work.
I can do VLOOKUP, but would rather not reformat my data.

Any thoughts? Thanks!- Hide quoted text -

- Show quoted text -