VBA Code for lookup with multiple criteria

[Shankar_Ganesh]

I need a VBA code to lookup from another sheet in the same workbook with multiple criteria.

sample workbook attached. the data will be more in number and also it is regular day-day activity. hence requesting for vba code to save time.

the vba code should combine a2&b2&c2 in sheet1 and lookup from sheet2 by combining column a-b-c and throw the value available in column F. result should be displayed in sheet1 - h2 cell.

 

[Kaper]

Please have a look on yesterday's thread: VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet I think the same approach could be used in your case

 

[Shankar_Ganesh]

Thank you Mr Kaper. But the version I am using is 2016. Can you please help me out in solving this issue in 2016 version.

 

[Kaper]

Indeed, XLOOKUP is not available in Exccel 2016 but you can use INDEX and MATCH instead

 

[kvsrinivasamurthy]

Try this code.

Sub Macro1()
Dim Lr1&, Lr2&
    
Lr1 = Sheets("SHeet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("SHeet2").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1")
.Range("h2").FormulaArray = "=IFERROR(IF($A2&$B2&$C2<>"""",INDEX(Sheet2!$F$2:$F$" & Lr2 & ",MATCH($A2&$B2&$C2,Sheet2!$A$2:$A$" & Lr2 & "&Sheet2!$B$2:$B$" & Lr2 & "&Sheet2!$C$2:$C$" & Lr2 & ",0)),""""),"""")"
.Range("h2").Copy .Range("h3:h" & Lr1)
End With

End Sub

 

[Shankar_Ganesh]

Indeed, XLOOKUP is not available in Exccel 2016 but you can use INDEX and MATCH instead

i got the solution. thank you Mr Kaper

 

[Shankar_Ganesh]

Try this code.

Sub Macro1()
Dim Lr1&, Lr2&
   
Lr1 = Sheets("SHeet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("SHeet2").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1")
.Range("h2").FormulaArray = "=IFERROR(IF($A2&$B2&$C2<>"""",INDEX(Sheet2!$F$2:$F$" & Lr2 & ",MATCH($A2&$B2&$C2,Sheet2!$A$2:$A$" & Lr2 & "&Sheet2!$B$2:$B$" & Lr2 & "&Sheet2!$C$2:$C$" & Lr2 & ",0)),""""),"""")"
.Range("h2").Copy .Range("h3:h" & Lr1)
End With

End Sub

Thanks a lot Mr kvsrinivasamurthy. the problem is solved. I am able to generate the output as required.

 

[kvsrinivasamurthy]

Welcome. Happy that your problem is solved.