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

#### bluefish44

##### Board Regular
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### mole999

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

#### AliGW

##### Banned

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

#### bluefish44

##### Board Regular
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")

#### mole999

##### Well-known Member
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

Last edited:

#### AliGW

##### Banned
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

##### Board Regular
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

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

#### AliGW

##### Banned
LOL!!! Thanks for letting us know.

Replies
5
Views
219
Replies
2
Views
203
Replies
5
Views
180
Replies
3
Views
150
Replies
1
Views
198

1,190,996
Messages
5,984,060
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back