Find last lookup value

Today we will find last lookup value from a range.

We will work on this sales summary report

Scenario: Here we have to find last lookup value from the Sales Summary report.

Step 1: In cell L2, Type the formula:

=INDEX($G:$G,MAX(IF($B$3:$B$14=J2,ROW($B$3:$B$14))))

Step 2: Press Ctrl+Shit+Enter instead of Enter as this is an array function.

Formula will look like: {=INDEX($G:$G,MAX(IF($B$3:$B$14=J2,ROW($B$3:$B$14))))}

Your output is ready, which is in Range(L2:L4).

Let's look into the formula:

We will check the formula step-by-step by pressing F9.

IF($B$3:$B$14=J2,ROW($B$3:$B$14)) = This is telling us the Row no. (4,6,8,11), where the respective User ID occurs.

MAX(IF($B$3:$B$14=J2,ROW($B$3:$B$14))) = This is telling us the Maximum Row no. (11) from the range for respective User ID.

=INDEX($G:$G,MAX(IF($B$3:$B$14=J2,ROW($B$3:$B$14)))) = This has returned our desired output (179).

-------------------------------------------------------------------------------------------------------------------------------------------------------

Download the file: LastLookup.xlsx

You can watch my video on YouTube for the same Query : https://youtu.be/vnhOT1LtN8A

Thanks for learning, will come soon with new post.