Editing Lookup Formula Error
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 |
Editing Lookup Formula Error
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 |
Editing Lookup Formula Error
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 . |
Editing Lookup Formula Error
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 . |
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 . . |
Editing Lookup Formula Error
it works... a million thanks!!!
Bud "Bud" wrote: 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 . . |
Editing Lookup Formula Error
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bud" wrote in message ... it works... a million thanks!!! Bud "Bud" wrote: 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 . . |
All times are GMT +1. The time now is 08:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com