How to validate and combine the two excel vlookup column results to one column

[vbvba]

Hi Guys,

I have two vlookup formulas in two columns of a sheet which validates the value whether it is "Pass" or "fail" from another sheet of the same workbook.

Here are the below 2 formulas :-

1) =IF(VLOOKUP(BY2&G2&BG2,Sheet2!A:I,9,0)>=BV2,"Pass","Fail")


2) =IF(VLOOKUP(BY2&G2&BG2&CB2,Sheet2!B:J,9,0)>=BV2,"Pass","Fail")

I would like to combine the two formulas using AND or OR operator to one column, however when i evaluate the formula using AND or OR operator i receive #N/A error from 2nd formula.
Kindly help me in correcting the below formula

=IF(OR(VLOOKUP(BY2&G2&BG2,Sheet2!A:I,9,0)>BV2,IF(VLOOKUP(BY2&G2&BG2&CB2,Sheet2!B:J,9,0)>=BV2,"Pass","Fail")),"Pass","Fail")

Also I have to run the formula using below code please help me in the same :-

Sub runtheformula()

Dim RowNo As Long


'update formulas
RowNo = Sheet1.Range("A1048576").End(xlUp).Row


Sheet1.Range("CI2:CI" & RowNo).FormulaR1C1 =

 "=IF(VLOOKUP(RC[-4]&RC[-73]&RC[-21],Sheet2!C1:C8,8,FALSE)>=RC[-6],""Pass"",""Fail"")"


Sheet1.Range("CI2:CI" & RowNo).Value = Sheet1.Range("CI2:CI" & RowNo).Value


Sheet2.Visible = xlSheetVeryHidden


MsgBox "Validation done"

 

[MrRedLi]

try the below formula:

=IF(OR(IFERROR(VLOOKUP(BY2&G2&BG2,Sheet2!A:I,9,0),"")>=BV2,IFERROR(VLOOKUP(BY2&G2&BG2&CB2,Sheet2!B:J,9,0),"")>=BV2),"Pass","Fail")

 

[vbvba]

Hi,

Thanks for the reply
When I try the formula I get all the values from top to bottom as Pass even though it is Fail
Can I attach the excel file or do you have any other formula ?

Thanks,