This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks

Personally, I to find those results, I would just do Data Filter
AutoFilter on the ODDS(Archices) sheet......It will bring up all the
instances of BOS, if there were one or fifty, or whatever. Then, that data
or parts of it can be copied to wherever else it might be needed..........

Vaya con Dios,
Chuck, CABGx3





"Jambruins" wrote:

This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks

=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)=ROW()-1,INDIRECT("'ODDS
(Archives)'!V"&SMALL(('ODDS
(Archives)'!$T$1:$T$5="BOS")*ROW($T$1:$T$5),ROW()+ SUM(--('ODDS
(Archives)'!$T$1:$T$5<$A$1))-1)),"")

This is an array formula. Paste the formula in and hit ctrl+shift+enter.
Copy down as needed. It outputs blanks if there are not anymore occurences.
I assumed A1 contains "BOS".

"Jambruins" wrote:

This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks

On Fri, 17 Mar 2006 10:55:26 -0800, Jambruins
wrote:

This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks




You could try this **array-entered** formula:

=IF(ROWS($1:1)COUNTIF(OFFSET(tbl,,,,1),$A$1),"",
LARGE((OFFSET(tbl,,,,1)=$A$1)*OFFSET(tbl,,2,,1),
COUNTIF(OFFSET(tbl,,,,1),$A$1)+1-ROWS($1:1)))

You will need to define rng (Insert/Name/Define) as

'ODDS (Archives)'!T1:Vnnnn

where nnnn is as small a number that will safely encompass your entire table.

Two reasons:
1. The smaller the array, the faster the formula will run.
2. An array formula may not refer to an entire column.

I don't know whether this formula will run quickly enough for you on your DB.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

Sloth, thanks that works great. Another similar question.

I would like the same thing to happen in cells B3, B4, etc. except instead
of taking the value from cell V I would like it to take the value from cell
U. How would I modify the formula you gave me? Thanks.



This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks

=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)=ROW()-1,INDIRECT("'ODDS
(Archives)'!U"&SMALL(('ODDS
(Archives)'!$T$1:$T$5="BOS")*ROW($T$1:$T$5),ROW()+ SUM(--('ODDS
(Archives)'!$T$1:$T$5<$A$1))-1)),"")

"Jambruins" wrote:

Sloth, thanks that works great. Another similar question.

I would like the same thing to happen in cells B3, B4, etc. except instead
of taking the value from cell V I would like it to take the value from cell
U. How would I modify the formula you gave me? Thanks.



This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks

change both formulas to the following. I had an error that wouldn't allow
for other tabs to work properly.

For column V:
=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)=ROW()-1,INDIRECT("'ODDS
(Archives)'!V"&SMALL(('ODDS
(Archives)'!$T$1:$T$5=$A$1)*ROW($T$1:$T$5),ROW()+S UM(--('ODDS
(Archives)'!$T$1:$T$5<$A$1))-1)),"")

For column U:
=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)=ROW()-1,INDIRECT("'ODDS
(Archives)'!U"&SMALL(('ODDS
(Archives)'!$T$1:$T$5=$A$1)*ROW($T$1:$T$5),ROW()+S UM(--('ODDS
(Archives)'!$T$1:$T$5<$A$1))-1)),"")

"Jambruins" wrote:

Sloth, thanks that works great. Another similar question.

I would like the same thing to happen in cells B3, B4, etc. except instead
of taking the value from cell V I would like it to take the value from cell
U. How would I modify the formula you gave me? Thanks.



This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOS NYY 3-Apr
PIT MIL 4-Apr
CIN NYM 4-Apr
PHI WAS 4-Apr
BOS NYY 4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks