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/ex...0corresponding
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

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
--
** John C **

"Connie Martin" wrote:

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/ex...0corresponding
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

Replace $D$2 with $D$1

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

"Connie Martin" wrote:

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/ex...0corresponding
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

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

On Oct 30, 6:49*pm, Connie Martin
wrote:
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/ex...aspx?pid=CL100...
* *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

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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/ex...0corresponding
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

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

"John C" wrote:

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
--
** John C **

"Connie Martin" wrote:

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/ex...0corresponding
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

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

"Sheeloo" wrote:

Replace $D$2 with $D$1

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

"Connie Martin" wrote:

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/ex...0corresponding
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

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

"Pete_UK" wrote:

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

On Oct 30, 6:49 pm, Connie Martin
wrote:
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/ex...aspx?pid=CL100...
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

This gives me 0 all the way down. Sorry.

"T. Valko" wrote:

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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/ex...0corresponding
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

Hmmm...

Works ok for me.

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
This gives me 0 all the way down. Sorry.

"T. Valko" wrote:

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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/ex...0corresponding
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

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

Pete

On Oct 30, 8:23*pm, Connie Martin
wrote:
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

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" wrote:

Hmmm...

Works ok for me.

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
This gives me 0 all the way down. Sorry.

"T. Valko" wrote:

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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/ex...0corresponding
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

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!



--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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" wrote:

Hmmm...

Works ok for me.

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
This gives me 0 all the way down. Sorry.

"T. Valko" wrote:

Try it like this:

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

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in
message
...
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/ex...0corresponding
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