Forum Discussion
Solved
IF & Vlookup funtion: =IF(VLOOKUP(D2,Data_Directory!$A$24:$A$429,1, FALSE)=D2, "OLD", "NEW")
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 🙂 🙂
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")
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")- Amazing, problem solved ! Thanks so much Harun24HR
- Glad to know! Then you can tick mark my answer so that other can know your problem is solved.
