Index Match problem

  • Thread starter Thread starter sharon
  • Start date Start date
S

sharon

When I Index and match a column it gives me the first match it finds. I
need it to look at the 2nd match instead of the first one it found.
How do I get it to skip over the first match it found??? Here is an
example

ColA-ColB--ColC-----------------Col D
joe----14----bob------=index($B$2:$B$5,match(c2,$A$2:$A$5,0))
bob---10----joe-------=index($B$2:$B$5,match(c3,$A$2:$A$5,0))
sue--- 9-----joe-------=index($B$2:$B$5,match(c4,$A$2:$A$5,0))
joe---18-----sue------=index($B$2:$B$5,match(c5,$A$2:$A$5,0))


The result is

ColD
10
14
14
9

and I want

ColD
10
14
18
9


Any help would be appreciated it.
 
Sharon

One way to do it:

In D2 enter:

=SUMPRODUCT(($A$2:$A$100=C2)*(COUNTIF(INDIRECT("A2:A" &
ROW($A$2:$A$100)),C2)=COUNTIF($C$2:C2,C2))*$B$2:$B$100)

Drag D2 down with the fill handle (the little square in the lower right
corner of the cell).

Please notice the use of mixed absolute ($$) and relative (no cash)
references.

The formula will work for any number of the same name in column A.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Leo Heuser said:
In D2 enter:

=SUMPRODUCT(($A$2:$A$100=C2)*(COUNTIF(INDIRECT("A2:A" &
ROW($A$2:$A$100)),C2)=COUNTIF($C$2:C2,C2))*$B$2:$B$100)
Click to expand...
....

The hardcoded portion, "A2:A", could be avoided by using OFFSET rather than
INDIRECT.

D2:
=SUMPRODUCT(($A$2:$A$5=C2)*(COUNTIF(OFFSET($A$2,0,0,
ROW($A$2:$A$5)-CELL("Row",$A$2:$A$5)+1,1),C2)
=COUNTIF($C$2:C2,C2))*$B$2:$B$5)

This only returns numbers. If column B could contain text, the longer array
formula

D2:
=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=C2,ROW($A$2:$A$5)),
COUNTIF(C$2:C2,C2))-CELL("Row",$A$2:$A$5)+1)

is required, which also works for numbers and avoids using arrays of ranges.
 
I don't know, Sharon. The formula should only return
0 for empty cells in column C.
You're welcome to attach a (zipped) copy of your workbook
to a personal mail, and I'll have a look at it.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Harlan Grove said:
...

The hardcoded portion, "A2:A", could be avoided by using OFFSET rather than
INDIRECT.

D2:
=SUMPRODUCT(($A$2:$A$5=C2)*(COUNTIF(OFFSET($A$2,0,0,
ROW($A$2:$A$5)-CELL("Row",$A$2:$A$5)+1,1),C2)
=COUNTIF($C$2:C2,C2))*$B$2:$B$5)
Click to expand...


Depending on the weather situation :-) I also use
=SUMPRODUCT(($A$2:$A$100=C2)*(COUNTIF(OFFSET($A$2,0,0,
ROW($A$2:$A$100)-1,1),C2)=COUNTIF($C$2:C2,C2))*$B$2:$B$100)

This only returns numbers. If column B could contain text, the longer array
formula

D2:
=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=C2,ROW($A$2:$A$5)),
COUNTIF(C$2:C2,C2))-CELL("Row",$A$2:$A$5)+1)

is required, which also works for numbers and avoids using arrays of
ranges.
Click to expand...

Nice one!

Is there any particular reason for using CELL("Row",$A$2:$A$5) instead
of ROW($A$2:$A$5)?

LeoH
 
Leo Heuser said:
"Harlan Grove" <[email protected]> skrev i en meddelelse ....
Is there any particular reason for using CELL("Row",$A$2:$A$5) instead
of ROW($A$2:$A$5)?
Click to expand...

CELL("Row",$A$2:$A$5)

is always equivalent to

CELL("Row",INDEX($A$2:$A$5,1,1,1))

As a practical matter, there may not be a difference, but ROW always returns
an array (even =ROW(A1) returns {1}). There are times when arrays screw up
formulas when scalars are expected, so I always use CELL("Row",..) when I
want only the topmost row of a range as a scalar.
 
Back
Top