IF & Vlookup funtion: =IF(VLOOKUP(D2,Data_Directory!$A$24:$A$429,1, FALSE)=D2, "OLD", "NEW")

[WishIWerentAN00b]

Hey guys, 

I am trying to say if we can find the value "D2" in the data directory sheet in the rows A24 through A429 then the output value should be "OLD", if we cannot find the value "D2" in the data directory sheet then the output should be "NEW". 

What is wrong with my formula? 

=IF(VLOOKUP(D2,Data_Directory!$A$24:$A$429,1, FALSE)=D2, "OLD", "NEW")

If it cannot find the value it returns an #N/A and not "NEW".

Thanks so much for the help :) :)

[#2 · Accepted solution]

@WishIWerentAN00b When vlookup will not find any matching value then it will return error. So, formula output will be error. Try to wrap vlookup formula by IFERROR function like

=IF(IFERROR(VLOOKUP(D2,Data_Directory!$A$24:$A$429,1, FALSE),"")=D2, "OLD", "NEW")

Better use MATCH function like

=IF(ISNUMBER(MATCH(D2,Data_Directory!$A$24:$A$429,0)), "OLD", "NEW")

[#3]

@WishIWerentAN00b As a variant:

=IF(ISERROR(VLOOKUP(D2,Data_Directory!$A$24:$A$429,1, FALSE)),"NEW","OLD")

[#4]

Amazing, problem solved ! Thanks so much @Harun24HR

[#5]

Glad to know! Then you can tick mark my answer so that other can know your problem is solved.