IF Formula with Vlookup Built in

[BlueRhinos]

Objective: I'd like to return specific value based on a lookup condition. Specifically return x IF the person's ID # is in a range of values. My thought is that we can accomplish by using a vlookup within an IF. Can anybody help with the syntax?

Thanks!

BR

 

[barry houdini]

You could use COUNTIF, e.g.

=IF(COUNTIF(A1:A100,C1),"In range","Not in range")

 

[rconverse]

Something like this:

Excel Workbook
	A	B	C	D
1	Subject:	4		IDs
2				1
3				2
4		Do other Stuff		3
5				4
6				5
7				6
8				7
Sheet1

If this isn't what you're looking for, maybe you could elaborate a bit.

HTH,
Roger

 

[BlueRhinos]

Thanks much Barry! It works..what is that countif doing. My understanding of the function was that it counted cells that met a certain condition.

 

[rconverse]

It is. It's assuming it will come back with a result of 1 or 0. 1 meaning it's on the list. The IF funtion is using that value as a true or false indicator. When the count returns a value of one, the IF funtion reads that as TRUE (this ID is in the list). If the count function returns a 0, the IF function views that as FALSE (this ID is not on the list).

I know you didn't ask me, but I'm bored.

Barry, what if the count is over 1 (duplicate in the list)? Won't that mess up the IF function?

 

[BlueRhinos]

Thanks not only for the solution, but the conceptual learning as well! Glad you were bored!

BR

 

[barry houdini]

Barry, what if the count is over 1 (duplicate in the list)? Won't that mess up the IF function?

No, Any non-zero number (even negative) will be deemed to be TRUE, only zero is FALSE......

 

[rconverse]

No, Any non-zero number (even negative) will be deemed to be TRUE, only zero is FALSE......

I did not know that. Thanks!