Using VLOOKUP in an If statement, but getting 0 as the answer?

[ThePangloss]

So I have a bunch of duplicates and uniques. I've marked all duplicates with numbers ranging from 1-10 depending on how many duplicates there are.

After this, on a different sheet to confirm which quantities are unique and which have duplicates I put

=IF(VLOOKUP(H2,Sheet2!$G$2:$O$100000,9,FALSE)="","unique","duplicate") where row 9 in sheet 2 contains blanks and these numbers from 1-10.

this returns a 0 for me. It works well if I don't include the $ anywhere, but when I include them I get 0's. I want it to search the whole array in the other sheet not just relatively to the H value given.

Any advice?

 

[James006]

Hi,

To combine If() function and VLookup() ... have a look at Debra's explanation ... and video ... http://www.contextures.com/xlFunctions02.html

 

[Trebor76]

Your formula will return the matched value from cell H2 to column O of Sheet2 not what's in row 9

What is in column O of Sheet2?

 

[konew1]

When the VLOOKUP returns from a null cell it will return zero, not "".
Your IF will always be FALSE, and return "duplicate"

Try
=IF(VLOOKUP(H2,Sheet2!$G$2:$O$100000,9,FALSE)>0,"duplicate","unique") or
=IF(VLOOKUP(H2,Sheet2!$G$2:$O$100000,9,FALSE)=0,"unique","duplicate")