Excel formula to find 1st 2nd (second) 3rd (third) to Nth occurrence in range and match multiple conditions through different columns

Query : I have database containing many columns, i have different tab/sheet for report sheet, in that I have to match values from two columns and extract matching values, problem is that my database there are same values in all two columns, so i want to match & extract 1st instance for 1st case, 2nd instance for second case and so on.

Data is in sheet in below format:




I want report in below format:



Solution: We can use an array formula to match multiple conditions & extract values, but I always try to avoid array formula since I believe it is heavy on sheet when it contains large. This formula is unique since I have tried using Large() function & used index to receive an array value in single column.

I have use following formula:

=INDIRECT(ADDRESS(LARGE(
                                                          INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1),
                                                          SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1),
                                                          COLUMN(D1),1,1,"DATA"))


Step 1: Get row number for matching condition usinh Index Formula:

INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1)

Step 2: To handle repeated values; we need to first fiind total matching values form that total count we will deduct instance of repetation at particular row, this will give us largest matching value for specific cell. Eg.In given report XYZ is repeated 3 times, but for finding value in cell C1 we will find third largest value, for finding value in cell C2 we will find second largest value & for finding value in cell C3 we will find first largest value. This can be arrived by using:

SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1)

Step3: If we put array result of step1 & result of step 2 in function Large() we will get row number of matching value, so formula will be :


=LARGE(INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1), SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1)

Step 4: Using Address formula to convert row number to cell refferrence (address) of destination cell.

=ADDRESS( step3 formula , COLUMN(D1),1,1,"DATA")

Here we used COLUMN(D1) because required result in column D.

Step 5: Using Indirect() function to converer cell address in  Step 4 to required result.


Download Example file

Please give your feedback & you can ask your query in iur open form www.ExcelVbaLab.Com

Cheers!!