Formula help...

[Guest]

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

 

[Guest]

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

 

[Guest]

=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".

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Guest]

=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)),"")

 

[Guest]

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()+SUM(--('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()+SUM(--('ODDS
(Archives)'!$T$1:$T$5<>$A$1))-1)),"")