Combine Countif and Vlookup

[cyliyu]

I have 2 columns with 2 formula.
Column M : =IF(J10="","",IF(COUNTIF(Sheet1!$D$6:$K$30,J10),"Car",""))
Column N : =IF(Sheet2!$F2="","",(VLOOKUP(J10,Sheet2!$B$2:$F$332,5,0)))

I tried to combine this 2 formula into one column but somehow it doesn't work.
=AND(J10="",Sheet2!$F2=""),"",(IF(Countif(Sheet1$D$6:$K$30,J10),"Car",(VLOOKUP(J10,Sheet2!$B$2:$F$332,5,0)),"+",))

Any help?

 

[NARAYANK991]

Hi ,

In the absence of a complete statement of the logic , try this :

=IF(OR(J10="", Sheet2!$F2=""),"",IF(COUNTIF(Sheet1!$D$6:$K$30,J10),"Car",VLOOKUP(J10,Sheet2!$B$2:$F$332,5,0)))

Narayan

 

[cyliyu]

Thanks, Narayan.

I have missed out the spreadsheet.
Please find attached file the update.
The formula only works for Vlookup section but not the countif.

 

[NARAYANK991]

Hi ,

What explains the outputs in E10 , E13 and E29 ?

Narayan

 

[cyliyu]

Hi ,

What explains the outputs in E10 , E13 and E29 ?

Narayan

Sorry for the errors.
E10 and E13 should follow Column K10 and K13.
H29 - When the number found and match in the "Ref" sheet, a "yes" will be shown.
I have updated the spreadsheet with the correction.
Eventually, I hope to remove column K, L and H and all the results will be found in Column E automatically to avoid human errors when transfer and copy the info from columns K, L and H into E.

 

[NARAYANK991]

Hi ,

Try this :

=IF(C10="","",IF(ISNUMBER(MATCH(C10&"_"&D10,Ref!$Q$3:$Q$300,0)),"Yes",IF(COUNTIF(DPList!$D$6:$K$30,C10&" / "&D10),"Car",IF(VLOOKUP(C10&" / "&D10,PFList!$B$2:$F$21,5,0) = "","",(VLOOKUP(C10&" / "&D10,PFList!$B$2:$F$21,5,0))))))

Narayan

 

[cyliyu]

Thanks, Narayan.

You help to solve the manual process and prevent human errors.
Appreciate your help.