VLOOKUP Multiple Occurrence for Same Lookup Value

Hello Friends,

Need Your Help to accomplish VLOOKUP for multiple instances of a same Lookup value

First let me explain about my file:-

Table~1:-

1. B2:B19 I have list of items which required to build a product
2. D2:D19 I have list of shortages against each item
3. C2:C19 I have list of dates when each item shortage will get fulfilled

Table~2:-

1. C22:C31 I have picked the largest 10 different dates from Table1 by using LARGE formula.

Here I need your help,

Against each date which is update C22:C31, respective shortage qty has to get VLOOKUPED.

When there is a same lookup value, it has to keep pick next value (Shortage Qty) for same date.

Note: If we can achieve this with formula, it would be really great!

Immediate help is really appreciated!

Thanks & Regards,
Rajeshkumar R

hi Rajesh. try this array formula in D22:
=INDEX($D$2:$D$19,SMALL(IF($C$2:$C$19=C22,ROW($C$2:$C$19)),COUNTIF(C$22:C22,C22))-ROW($C$2)+1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.