Main Problem statement : To retrieve multiple matches in excel.
I have 3 columns starting from AD to AF.AD has multiple matches.I need to retrieve AF values for matched AD value.
I searched on net and got the below formula.
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
But I am trying to implement my own solution.I am trying to use MATCH function as range in VLOOKUP function.Then change the range of MATCH function to retrieve 2nd match and so on.Continue this until there is no match found.
Here is what I tried which is giving an #NAME error.
=VLOOKUP(AD3,AD&EVAL("=MATCH(AD3,AD:AD,0)"):AF1000,3,0)
Also,it would be very appreciated if there is another solution to the main problem statement.
If this is the right direction to proceed,please help to complete this formula.
Thanks.
MATCH(AD3,AD:AD,0)
is always going to return 3 if you get it working. Are the multiple results returned to a single cell or are you filling the formula down/right for subsequent returns? – user4039065