SOLVED

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

Occasional Contributor

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  

 

4 Replies
best response confirmed by WishIWerentAN00b (Occasional Contributor)
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")

@WishIWerentAN00b As a variant:

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

 

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