Editing Lookup Formula Error

[Bud]

I've adapted the following formula from "How to lookup a value..." from
Office Online:

=IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))

date no1 no2 no3 no4 no5 1
6/7/2008 2 4 11 15 28 6/24/08
6/10/2008 8 13 14 23 30 6/28/08
6/14/2008 5 13 24 35 45
6/17/2008 10 12 18 30 36
6/21/2008 1 2 5 23 45
6/24/2008 1 4 37 45 46


However, the date that is returned is from the row BELOW the I would like.
In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
appear. Any suggestions? Many thanks.

Bud

 

[T. Valko]

It looks like the smallest number that is possible in your data set is the
number 1. Is that correct? It also looks like the numbers are in ascending
order from left to right. So, if my guess is correct then you should only
need to test the first column of the number data for the number 1.

To improve the formula based on my interpretation...

Enter this formula in H1. This will return the count of 1s in column B.

=COUNTIF(B2:B1007,1)

Enter this array formula** in G2:

=IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date. Copy down until you get blanks.

 

[Bud]

Thanks for your response. I should have been more specific. While the
smallest number in my dataset is 1, the largest is lookup number is 52 and
the remainder appear in row 1 to the right of the number 1 of my very brief
example. So my objective is to identify (i.e., list) each date that any of
the 52 possible numbers appear, regardless of the column (i.e., no1, no2,
no3, etc) that that specific number appeared. The dates are arranged in
increasing date order and the numbers are increasing in each row. Since
there are over 2,000 rows of data, I was thinking that I could copy an array
formula down and across (formatting as date) without having multiple columns
for each lookup number. Can you provide some guidance?

Oh and by the way, I have seen many of your posts and I must acknowledge how
insightful and accurate you are! Cheers.

Bud

 

[T. Valko]

Here's a small sample file that demonstrates this.

Bud.xls 21kb

http://cjoint.com/?bDd4VvyxrT

 

[Bud]

wonderful... i'll give it a try.

Here's a small sample file that demonstrates this.

Bud.xls 21kb

http://cjoint.com/?bDd4VvyxrT

--
Biff
Microsoft Excel MVP





.

 

[Bud]

it works... a million thanks!!!

Bud

wonderful... i'll give it a try.

 

[T. Valko]

You're welcome. Thanks for the feedback!