Using MATCH function as range in VLOOKUP function in Excel

0
votes

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.

1
excelexcel-formula
you could try indirect() instead of match() – Solar Mike
Beyond bad syntax, 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
Indirect() will give value if you specify the cell reference.I want the row_no. – user3126632
@Jeeped I want to combine everything in 1 formula.So if there are multiple returns I have to compare them and return TRUE if all results are same and FALSE if unequal. – user3126632
btw, that working array formula is a bit antiquated (pre-xl2010); I prefer an index/aggregate that does it a little easier. – user4039065

1 Answers

0
votes
  1. Main Problem statement : To retrieve multiple matches in excel.
  2. Now I have to get multiple matches and compare them if they are equal.

You seem to have an XY Problem. You are concentrating on resolving the first half of your formula problem while ignoring a simpler solution that would resolve the entire task.

=and(countif(ad:ad, ad3)=countifs(ad:ad, ad3, af:af, af3))

True if all related values in column AF where column AD is equal to AD3 are the same; false if column AF contains differing values.