|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
Editing Lookup Formula Error
wonderful... i'll give it a try.
"T. Valko" wrote:
Here's a small sample file that demonstrates this.
Bud.xls 21kb
http://cjoint.com/?bDd4VvyxrT
--
Biff
Microsoft Excel MVP
"Bud" wrote in message
...
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" wrote:
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$1 007=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.
--
Biff
Microsoft Excel MVP
"Bud" wrote in message
...
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$10 07=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
.
.
|