Here I want to retrieve values for  Data1, Data2, Data3 & Data4 from the given dataset (A1:F10) based on two lookup values that is EmpCode and Dept.

Formula used in range C13:F13

=VLOOKUP(A13&B13,CHOOSE({1,2,3,4,5},A1:A10&B1:B10,C1:C10,D1:D10,E1:E10,F1:F10),{2,3,4,5},0) with CSE

This can also be done using the Index/Match function. Below is the formula:

=INDEX($A$1:$F$10,MATCH(1,IF($A$1:$A$10=$A$13,IF($B$1:$B$10=$B$13,1)),0),{3,4,5,6}) with CSE

Is the post useful?

Kindly put your comment in the comment section and subscribe the blog:)