Cell for cell match and return offset cell

1
votes

With the countif formula that I've received from this question: Cell for cell match instead of value for value match, is there a formula retains the cell for cell match property as countif does AND, that outputs an offset cell from the lookup range, if there is indeed a match?

Consider this scenario: enter image description here In this example, the formula:

=IF(B2<>"",IF(COUNTIF($B$2:B2,B2)<=COUNTIF($E$2:$E$7,B2),"Match",""),"") in cells C4 and C5 is matching for amounts that coincide with amounts in range E2 to E7 and returns "Match" if there indeed is a match.

By replacing the output string "Match" with an Index - Match formula, I obtain an almost perfect formula that does return an offset cell from the lookup range. =IF(B2<>"",IF(COUNTIF($B$2:B2,B2<=COUNTIF($E$2:$E$7,B2),INDEX($D$2:$D$6,MATCH(B2,$E$2:$E$7,0)),""),"")

enter image description here

You may notice however, that it returns "xxxx McDonalds" in cell C3 instead of "xxxx Subway" and that, is the problem.

1
excelexcel-formula

1 Answers

2
votes

It can be simplified with the use of AGGREGATE to:

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($E$2:$E$7)/($E$2:$E$7=B2),COUNTIF($B$2:B2,B2))),"no match")

enter image description here