SOLVED

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

Copper Contributor

# 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 :) :)

4 Replies
best response confirmed by WishIWerentAN00b (Copper Contributor)
Solution

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

@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")``

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

@WishIWerentAN00b As a variant:

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

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

Amazing, problem solved ! Thanks so much @Harun24HR

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

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