Vlookup in array formula

[Guest]

Greetings and TIA for your help on this one
Lets say I have a lookup table with range name "colors"
Pear Gree
Apple Gree
Plum Red , etc
In the main database table I have a field header "Fruit
Using an array formula, I want to count the number of items that are "Green" when refered to the lookup table
I've tried: ={sum(--(vlookup(Fruit,Colors,2,0)="Green")} but I just get 1 or zero, dependent on whether the first cell in "Fruit" meets the criteria or not
Any suggestions would be gratefully received

 

[Jason Morin]

Your post is confusing, but if you want to count the
number of times "Green" appears in the second column of
the range "colors":

=SUMPRODUCT(--(INDEX(colors,,2)="Green"))

HTH
Jason
Atlanta, GA

-----Original Message-----
Greetings and TIA for your help on this one:
Lets say I have a lookup table with range name "colors":
Pear Green
Apple Green
Plum Red , etc
In the main database table I have a field header "Fruit"
Using an array formula, I want to count the number of

items that are "Green" when refered to the lookup table.

I've tried: ={sum(--(vlookup(Fruit,Colors,2,0)="Green")}

but I just get 1 or zero, dependent on whether the first
cell in "Fruit" meets the criteria or not.

 

[JE McGimpsey]

If I understand you correctly:

=SUMPRODUCT(--(OFFSET(Colors,0,1,,1)="Green"))

 

[Guest]

Thanks for your reply
please suffer me a little more, I need to explain the problem better
I have a database containing a column range called "Fruit" (with 1000s of records
I have a seperate lookup table called "colors" which lists fruits in the first column and colors in the second
There is no colors field in the databas
I would like count how many records in the database have fruits whose color is given as "green" in the lookup table
I've tried using vlookup in an array formula (see my original post) but no joy so fa
Thanks again for your time

 

[Frank Kabel]

Hi
why not add a helper column in the first table with a
simple lookup tot rasnfer all colors to the first table.
After this use COUNTIF for example on this helper column

-----Original Message-----
Thanks for your reply,
please suffer me a little more, I need to explain the problem better:
I have a database containing a column range

called "Fruit" (with 1000s of records)

I have a seperate lookup table called "colors" which

lists fruits in the first column and colors in the second.

There is no colors field in the database
I would like count how many records in the database have

fruits whose color is given as "green" in the lookup table.

I've tried using vlookup in an array formula (see my

original post) but no joy so far

 

[Peo Sjoblom]

One possible way

=SUM((INDEX(Colors,,1)=TRANSPOSE(Fruits))*(INDEX(Colors,,2)="green"))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

Thanks for your reply,
please suffer me a little more, I need to explain the problem better:
I have a database containing a column range called "Fruit" (with 1000s of records)
I have a seperate lookup table called "colors" which lists fruits in the

first column and colors in the second.

There is no colors field in the database
I would like count how many records in the database have fruits whose

color is given as "green" in the lookup table.

 

[Harlan Grove]

One possible way

=SUM((INDEX(Colors,,1)=TRANSPOSE(Fruits))*(INDEX(Colors,,2)="green"))

entered with ctrl + shift & enter

...

A variation on this would be

=SUMPRODUCT(COUNTIF(Fruits,INDEX(Colors,,1)),--(INDEX(Colors,,2)="Green"))

Then the Fruits range could be any single area range.

 

[Guest]

Thanks Harlan & Peo for your most excellent solutions