Return character X when a item is found in Vlookup and blank if there is no data in the cell.

[HoboJoenME]

Good morning,

So I have a spreadsheet where I want to look up a name and have it return as "Have" if it is present in the data sheet and "Don't have" if it isn't there on another sheet. I used the formula =IF(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE)=FALSE,"Don't Have","Have") to see if this worked but when it sees that the name isn't there, it just says N/A, it does come back with "Have". R6 is the cell where the name is, '2 Star Bots'!$A$3:$A$162 is the range where all the names are located. Even better if I could have it just mark X that it is present in the data sheet and blank if it isn't but I'll take what I can get. Any suggestions?

 

[Brunts]

Haven't tried it but think it should do the trick
=IF(ISERROR(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE),"Don't Have","Have")

 

[HoboJoenME]

Haven't tried it but think it should do the trick
=IF(ISERROR(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE),"Don't Have","Have")

It says I've entered too many arguments for this function.

 

[Fluff]

How about

=IF(ISNA(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE)),"Don't Have","Have")

 

[KRice]

A couple more ideas...

Book1
	A																	R	S	T
2	Items
3	z
4	y
5	x																	Queries
6	c																	y	have	have
7	a																	a	have	have
8	b																	c	have	have
9																		d	don't have	don't have
Sheet1

Cell Formulas
Range	Formula
S6:S9	S6	=IF(SUMPRODUCT(--($A$3:$A$8=R6)),"have","don't have")
T6:T9	T6	=IF(ISNUMBER(MATCH(R6,$A$3:$A$8,0)),"have","don't have")

 

[steve the fish]

How about:

=IF(ISNUMBER(MATCH(R6,'2 Star Bots'!$A$3:$A$162,0)),"X","")

 

[Brunts]

Haven't tried it but think it should do the trick
=IF(ISERROR(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE),"Don't Have","Have")

****! =IF(ISERROR(VLOOKUP(R6,'2 Star Bots'!$A$3:$A$162,1,FALSE)),"Don't Have","Have")

 

[HoboJoenME]

A couple more ideas...

Book1
	A																	R	S	T
2	Items
3	z
4	y
5	x																	Queries
6	c																	y	have	have
7	a																	a	have	have
8	b																	c	have	have
9																		d	don't have	don't have
Sheet1

Cell Formulas
Range	Formula
S6:S9	S6	=IF(SUMPRODUCT(--($A$3:$A$8=R6)),"have","don't have")
T6:T9	T6	=IF(ISNUMBER(MATCH(R6,$A$3:$A$8,0)),"have","don't have")

A couple more ideas...

Book1
	A																	R	S	T
2	Items
3	z
4	y
5	x																	Queries
6	c																	y	have	have
7	a																	a	have	have
8	b																	c	have	have
9																		d	don't have	don't have
Sheet1

Cell Formulas
Range	Formula
S6:S9	S6	=IF(SUMPRODUCT(--($A$3:$A$8=R6)),"have","don't have")
T6:T9	T6	=IF(ISNUMBER(MATCH(R6,$A$3:$A$8,0)),"have","don't have")

Yes, that did the trick! Thank you very much!

 

[KRice]

We're happy to help. Welcome to the Board!