Vlookup - if there are multiple occurances, is one of them not equal to 0?

[bluefish44]

I am looking to create a vlookup where if there is at least one occurance that is not 0, then there is a match and the vlookup it will return a "Y"

example:
Lookup value is Panda

Column A I have the following words going down with corresponding values in column B
Panda, 0
Tiger, 0
Panda, 0
Lion, 1
Panda, 1
Tiger, 0
Panda, 0

If I do a regular vlookup on Panda it will go to the first one which shows a 0, so the formula will not return a "Y" even though there are multiple occurances of the word Panda and at least one of them has a value that is not 0?

Any ideas?

 

[mole999]

vlookup generally works down a list and returns only the first match, which is why the list needs to be sorted

 

[AliGW]

Try this instead:

=if(COUNTIFS($A$1:$A$10,"Panda",$B$1:$B$10,">0")>0,"Y","N")

 

[bluefish44]

Thanks - AliGW - I changed your formula a little and it seems to work for me (Iplaced this formula in C2 to drag down)

IF(COUNTIFS($A:$A,A2,$B:$B,"<>0")>0,"Y","N")

 

[AliGW]

Glad to have helped!

 

[mole999]

Thanks - AliGW - I changed your formula a little and it seems to work for me (Iplaced this formula in C2 to drag down)

IF(COUNTIFS($A:$A,A2,$B:$B,"<>0")>0,"Y","N")

You can do that, but you are referring to two million or so rows, and I doubt there are that many Pandas

 

[AliGW]

You can do that, but you are referring to two million or so rows, and I doubt there are that many Panadas

I doubt there are any Panadas ... :D

 

[bluefish44]

oh wait - Actually I'm still having an issue

Not sure how to attach so I'll map it below:
One additional thing - If the value in column B is not 0, it will be a string of text, not a number
The formula I want to put in C2 and drag down
In this case Panda gets a Y for all occurences because at least one of the values is not 0 (in this case there are two values that are not 0 (Harry and Jim))
Tiger is the only one here that will get a"N" because every occurence is a 0

		formula
Panda	0	y
Panda	0	y
Panda	0	y
Panda	harry	y
Panda	0	y
Panda	jim	y
Panda	0	y
Lion	0	y
Lion	0	y
Lion	frank	y
Lion	0	y
Lion	0	y
Lion	0	y
Tiger	0	n
Tiger	0	n
Tiger	0	n
Tiger	0	n
Tiger	0	n

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[bluefish44]

oh geez - I need to lay off the crack - the formula is working

 

[AliGW]

LOL!!! Thanks for letting us know.