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
<Demosthe...@discussions.microsoft.com> 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
<Demosthe...@discussions.microsoft.com> 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 -