Help If vlookup

[jynxy]

Hi,

im trying to do a vlookup and if there is a value then show "CH", i can get this to work until there is no result and i have tried IFNA, IFERROR etc but always get the incorrect result.

basically im trying to lookup a date, check if a 3rd column has a value and if so return "CH" otherwise carry on with the next if, the problem is i can only get this to work with a double if, which then stops the next if working if no date is found, or it will show CH even if there is no value in the 3rd column. below is the sort of thing i am looking for, but i need it to ignore if there is no date and no value in 3rd column, hope this makes sense.

=if(vlookup(A1,c1:f20,3,FALSE)="y","CH",if(.......

 

[Saurabhj]

Hi, welcome to MrExcel board.

kindly share the excel sheet also using XL2BB to identify the issue.

Thanks,
Saurabh

 

[etaf]

you will get either a 0 returned if the date does exist but no value in column 3
or a N/A error if no date exists

does this work
=IF(IFERROR(NOT(VLOOKUP(A1,C1:F20,3,FALSE)=0),FALSE),"CH","New IF")

I suspect there maybe a cleaner way to dp the test
(IFERROR(NOT(VLOOKUP(A1,C1:F20,3,FALSE)=0),FALSE)
This will Text for an Error and return FALSE
also test for a no entry in column 3
BUT if zero is entered into column 3 , then this will also return a false
as a 0 or no entry in column 3 returns a zero
as show below

also the 3 conditions , highlighted in yellow,orange,geen - just for view here

Book3
	A	B	C	D	E	F	G
1	1/8/21		1/1/21		0		CH
2			1/2/21		1
3			1/3/21
4	1/4/21		1/4/21				New IF
5			1/5/21
6			1/6/21
7	2/12/21		1/7/21				New IF
8			1/8/21		2
9			1/9/21
10			1/10/21
11			1/11/21
12			1/12/21		3
13			1/13/21
Sheet2

Cell Formulas
Range	Formula
G1,G7,G4	G1	=IF(IFERROR(NOT(VLOOKUP(A1,C1:F20,3,FALSE)=0),FALSE),"CH","New IF")

 

[jynxy]

Thanks @etaf you are a legend here is a ? from me.

 

[etaf]

you are welcome