Hi,
I am trying to look up values using if condition combined with vlookup and hlookup. The values are in a separate workbook/worksheets to the target workbook/worksheet.
Below is the formula which I have at the moment. But I get 'N/A' error.
=IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Worker",
VLOOKUP($A2,'C:\ABC\[Workbook1.xlsm]WorkerNames'!$B$2:$Z$100,5,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Employer",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Provider",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE))))
--- Jet
I am trying to look up values using if condition combined with vlookup and hlookup. The values are in a separate workbook/worksheets to the target workbook/worksheet.
Below is the formula which I have at the moment. But I get 'N/A' error.
=IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Worker",
VLOOKUP($A2,'C:\ABC\[Workbook1.xlsm]WorkerNames'!$B$2:$Z$100,5,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Employer",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Provider",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE))))
--- Jet