Vlookup in array formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...
items that are "Green" when refered to the lookup table.
I've tried: ={sum(--(vlookup(Fruit,Colors,2,0)="Green")}
Click to expand...
but I just get 1 or zero, dependent on whether the first
cell in "Fruit" meets the criteria or not.
 
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
 
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
Click to expand...
called "Fruit" (with 1000s of records)
I have a seperate lookup table called "colors" which
Click to expand...
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
Click to expand...
fruits whose color is given as "green" in the lookup table.
I've tried using vlookup in an array formula (see my
Click to expand...
original post) but no joy so far
 
One possible way

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

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom


David said:
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
Click to expand...
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
Click to expand...
color is given as "green" in the lookup table.
 
One possible way

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

entered with ctrl + shift & enter
Click to expand...
...

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.
 
Back
Top