FETCHING DATA FROM A TABLE BASED ON MORE THAN ONE LOOKUP VALUE

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:)

FORMULA TO ASSIGN RANK (RANK FUNCTION ALTERNATIVE)

We have a in-built RANK function in excel to assign rank to our data but the issue with this function is it skips ranking(s) if there is a tie. In above case, it assigns 3 to 40 and 5 to 38 and skips ranking 4. But the formula that has been used in column C is a perfect substitute for it and returns the correct rankings even if there are duplicate values in our data. It can be used in place of excel RANK function.

Formula used in C2:C11

=SUM(0+(FREQUENCY(IF($A$2:$A$11>A2,$A$2:$A$11),$A$2:$A$11)>0))+1 with CSE

You can use "<" in place of  ">" if you want to assign the ranking in ascending order.

Thanks for reading it:)

Kindly put your valuable comment in the comment box and follow my blog.