[Solved] Get Values against each duplicate entry

[Excel-rate]

Question: Is there any formula or VBA function to get the next value for each duplicate entry from Table-1 like in Table-2 for name "Saood" 2nd time value should be occured 51 and 3rd time should be 91 accordingly in table-2. Im using below formula in Column I , but it is not working properly. Please help

=VLOOKUP($G3,INDIRECT("A"&MATCH($G3,$A$3:$A$18,0)+COLUMNS($J$3:J3)&":B16"),2,0)

 

[Lutfor Rahman Shimanto]

Question: Is there any formula or VBA function to get the next value for each duplicate entry from Table-1 like in Table-2 for name "Saood" 2nd time value should be occured 51 and 3rd time should be 91 accordingly in table-2. Im using below formula in Column I , but it is not working properly. Please help

=VLOOKUP($G3,INDIRECT("A"&MATCH($G3,$A$3:$A$18,0)+COLUMNS($J$3:J3)&":B16"),2,0)


View attachment 1163

Hello Saood Ahmed

Thanks for reaching out and sharing your problem. The requirements you described can be reached using a Helper column.

I am happy to inform you that I developed an idea and some formulas using the COUNTIF, INDEX and MATCH functions.

Follow these steps:
Step 1: Select cell C3 => Apply the formula below => Drag the Fill Handle icon to cell C18.

=COUNTIF($A$3:$A3, $A3) & A3

​

Step 2: Select cell I3 => Apply the following formula => Drag the Fill Handle icon to cell I18.

=INDEX($A$3:$C$18,MATCH(COUNTIF($G$3:$G3, $G3) & G3,$C$3:$C$18,0),2)

​

I am attaching the solution workbook for better understanding. Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Ihab]

I'm glad this is my first post
This solution is without using helper column

 

[Lutfor Rahman Shimanto]

I'm glad this is my first post
This solution is without using helper column

Hello Ihab

Welcome to ExcelDemy Forum. Thank you for sharing your expertise with our ExcelDemy Forum!

The extra values you added are effective. We appreciate you sharing your formulas with us, and we feel it will benefit others who visit our thread.

Formulas (Contributed by Ihab):

=IFERROR(INDEX($B$3:$B$18,SMALL(IF(G3=$A$3:$A$18,ROW($A$3:$A$18)-MIN(ROW($A$3:$A$18))+1,""),COUNTIF(G3:$G$3,G3))),"Not available")

​

Thanks once again. Stay blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy