INDEX, SMALL, ROW

[Connie Martin]

Here's an array formula I found in Excel Help that works almost perfectly.
There's a glitch and I don't know how to fix it. This is the formula:
=INDEX($D$2:$E$5000,SMALL(IF($D$2:$D$5000=$U$2,ROW($D$2:$D$5000)),ROW(1:1)),2)

I know about the CTRL+SHIFT+ENTER for an array formula, so that is not the
problem.

This formula is looking for the name in U2 within Col. D (which appears
multiple times) and returning the corresponding number in Col. E.

The glitch: the first time the name appears in Col. D is in row 26. It's
again in row 27, 28 and 29. But what the formula is returning is the
corresponding numbers in 26, 27, 28 and 30. Then the name appears in row 77,
78 and 79 and the formula returns 78, 79, 80. The pattern continues. See
below. Maybe that will give a better picture. I got this formula from:
http://office.microsoft.com/en-us/e...=CL100570551033#Return multiple corresponding
The formula for returning the row number works fine but the formula for
returning the corersponding value doesn't work. Here's what it's doing:

CORRECT ROW ROW RETURNED
26 26
27 27
28 28
29
30
77
78 78
79 79
80
81
82
91
92
96
97 97
98 98
99 99
100 100
101
120
121 121
122 122
123 123
124 124
125
134
135 135
136 136
137 137
138 138
139
143
144 144
145 145
146 146
147

 

[John C]

It's your absolute ranges, you start from row 2 in those ranges (i.e.:
$D$2:$D$5000). You should change these to start on row 1, i.e: $D$1:$D$5000

 

[Sheeloo]

Replace $D$2 with $D$1

You are calculating from Row 2 so your nos are off by 1.

 

[Pete_UK]

This part in the middle of your formula:

ROW($D$2:$D$5000)

will return 2 if there is a match on the first row. However, as your
INDEX array starts on row 2 then this will get the data from the
second element of the array, i.e. from row 3. So, to rectify it change
that middle part to:

ROW($D$2:$D$5000)-1
or
ROW($D$1:$D$4999).

Hope this helps.

Pete

 

[T. Valko]

Try it like this:

=INDEX(E$2:E$5000,SMALL(IF(D$2$5000=$U$2,ROW(E$2:E$5000)),ROWS(A$1:A1))-min(row(E$2:E$5000))+1)

 

[Connie Martin]

Amazing! That works. My data starts in row 2. That's why I put D2 in the
formula. Thank you so very, very much!! Connie

 

[Connie Martin]

Yes, just as John C said. Thank you! My data starts in row 2, so that's why
I thought I had to put D2 in the formula. Thank you for responding! Connie

 

[Connie Martin]

Thank you, Pete. As I mentioned to the others, my data starts in row 2 so I
thought I had to have the formula starting in row 2. Thank you! I'm so
pleased to get this thing working! Connie

 

[Connie Martin]

This gives me 0 all the way down. Sorry.

Try it like this:

=INDEX(E$2:E$5000,SMALL(IF(D$2$5000=$U$2,ROW(E$2:E$5000)),ROWS(A$1:A1))-min(row(E$2:E$5000))+1)

 

[T. Valko]

Hmmm...

Works ok for me.

 

[Pete_UK]

You're welcome, Connie - thanks for feeding back (to us all).

Pete

 

[Connie Martin]

I'm sorry. I tried it again and it worked. I failed to do CTRL+SHIFT+ENTER
and that's why it didn't work. But it DOES work, and I want to thank you
very much. Just a suggestion, though---for us people who are not Excel gurus
it would be good to just mention that it's an array formula and one must
press CTRL+SHIFT+ENTER. Thank you. Connie

 

[T. Valko]

Thanks for the update.

You're right about the array enter reminder. But, in my defense, in your
first post you mentioned that you knew about array entering and the formula
I suggested is just a different way to write the formula you posted so I
assumed you knew that it also needed array entry.

Back to the problem with the formula...

When you index a range the range is held in a specific sequence. I like to
think of this sequence as positions. The positions are numbered from 1 to
the total count of cells in the indexed range. If the indexed range was
A10:A14 then this range is indexed (has the positions) like this:

A10 = position 1
A11 = position 2
A12 = position 3
A13 = position 4
A14 = position 5

So, when you use the ROW function to tell INDEX which position you want then
the arguments used in ROW have to match the positions in INDEX. Like this:

INDEX(A10:A14,........ROW(A10:A14).....

As written that does not match. We have 5 items indexed in positions 1, 2,
3, 4, and 5 but the ROW function is returning 10, 11, 12, 13, and 14. So, we
need to convert ROW(A10:A14) so that it matches the positions of the indexed
range. We need to convert 10, 11, 12, 13, 14 to 1, 2, 3, 4, 5.

Here's the most "user-proof" method to do that:

ROW(A10:A14)-MIN(ROW(A10:A14))+1

This expression evaluates to:

10-10+1 = 1
11-10+1 = 2
12-10+1 = 3
13-10+1 = 4
14-10+1 = 5

Now we have ROW values that match the positions of the indexed range.

Hopefully that isn't more confusing!