Vlookup for 1st,2nd,3rd,...Nth Value

Today we will find 1st,2nd,3rd,...Nth value from a range.

We will work on this sales summary report

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

Step 1: In cell M2, Type the formula:

=IFERROR(INDEX($G:$G,SMALL(IF($B$3:$B$14=K2,ROW($B$3:$B$14)),$M$1)),"Do not exist")

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

Formula will look like: {=IFERROR(INDEX($G:$G,SMALL(IF($B$3:$B$14=K2,ROW($B$3:$B$14)),$M$1)),"Do not exist")}

Your output is ready, which is in Range(M2:M4).

Let's look into the formula:

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

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

SMALL(IF($B$3:$B$14=K2,ROW($B$3:$B$14)),$M$1) = This is telling us the Respective Row no. (6) from the range for respective User ID.

INDEX($G:$G,SMALL(IF($B$3:$B$14=K2,ROW($B$3:$B$14)),$M$1)) = This has returned our desired output, which is 266.

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

Download the file: Vlookup 1st,2nd,3rd...Value.xlsx

Thanks for learning, will come soon with new post.