Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
I need a formula in the spreadsheet that will perform the following:
IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
=Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)*(V3=
FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Wow! Thanks a lot Tom, I will give this a try.
"Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)*(V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Tom,
Thanks, this VERY NEARLY works. It seems that for every item, the return is exactly two rows below where it should be. My first row of data in the source sheet is row 3 (sorry, I should have told you this before). How do I adjust this formula to correct for the two rows? Thanks again! "Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)*(V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Hi Tom
small variation :-) =Index(Sheet1!$A$2:$A$500,MATCH(1,(V1=FL)*(V1<=FH )*(V2=AL)*(V2<=AH)*( V3= FeL)*(V3<=FeH),0)) -- Regards Frank Kabel Frankfurt, Germany "quartz" schrieb im Newsbeitrag ... Wow! Thanks a lot Tom, I will give this a try. "Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)* (V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Never mind Tom. I put in the first parameter wrong. My first range (INDEX)
needs to start at row 1 (I started it at row 3). This works great. Thanks a million!!! "quartz" wrote: Tom, Thanks, this VERY NEARLY works. It seems that for every item, the return is exactly two rows below where it should be. My first row of data in the source sheet is row 3 (sorry, I should have told you this before). How do I adjust this formula to correct for the two rows? Thanks again! "Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)*(V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Actually, I made the same mistake in my example. But you are correct, the fix is to start in row 1 for the base of the index function. -- Regards, Tom Ogilvy "quartz" wrote in message ... Never mind Tom. I put in the first parameter wrong. My first range (INDEX) needs to start at row 1 (I started it at row 3). This works great. Thanks a million!!! "quartz" wrote: Tom, Thanks, this VERY NEARLY works. It seems that for every item, the return is exactly two rows below where it should be. My first row of data in the source sheet is row 3 (sorry, I should have told you this before). How do I adjust this formula to correct for the two rows? Thanks again! "Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)*(V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenging long ARRAY formula needed - Can this be done?
Thanks much for your post Frank. I will try your solution as well and keep
both versions documented in my library. Thanks again! "Frank Kabel" wrote: Hi Tom small variation :-) =Index(Sheet1!$A$2:$A$500,MATCH(1,(V1=FL)*(V1<=FH )*(V2=AL)*(V2<=AH)*( V3= FeL)*(V3<=FeH),0)) -- Regards Frank Kabel Frankfurt, Germany "quartz" schrieb im Newsbeitrag ... Wow! Thanks a lot Tom, I will give this a try. "Tom Ogilvy" wrote: =Index(Sheet1!$A$2:$A$500,Small(if((V1=FL)*(V1<=F H)*(V2=AL)*(V2<=AH)* (V3= FeL)*(V3<=FeH),row($A$1:$A$500)),1),1) Where FL (Fund_Lo) would be something like Sheet1!$B$2:$B$500 V1 (Value1) would be like Sheet3!$A$1 Entered as an Array formula -- Regards, Tom Ogilvy "quartz" wrote in message ... I need a formula in the spreadsheet that will perform the following: IF Value1 is = Fund_Lo AND Value1 is <= Fund_Hi AND IF Value2 is = Account_Lo AND Value2 is <= Account_Hi AND IF Value3 is = Ferc_Lo AND Value3 <= Ferc_Hi THEN RETURN the contents of Column "A" on the row in which the FIRST TRUE result occurs. NOTES: Fund_Lo, Fund_Hi, Account_Lo, Account_Hi, Ferc_Lo, and Ferc_Hi are all: 1) Values; 2) Reside in a single source sheet in a list of about 500 rows; Value1, Value2, and Value3 a 1) Values; 2) Reside in a separate sheet in the same file; The item to be RETURNed: 1) Is textual 2) Resides in the same sheet as Fund_Lo, Fund_Hi, etc. Is this even possible? I would presume an ARRAY formula is needed since the source is a list. I spent all day yesterday trying to crack this. Can someone please post a solution? Also, the columnar order of the contents can be changed if needed for lookups, etc. Thanks in advance. Example of Source Sheet (I hope the spacing comes out ok): Return Fund_Lo Fund_Hi Acct_Lo Acct_Hi Ferc_Lo Ferc_Hi 1A 100 101 1001 1030 140 142 4EB 102 105 4001 4030 140 143 3F 106 110 3001 3500 220 225 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Help Needed | Excel Worksheet Functions | |||
Answers needed for challenging formula | Excel Worksheet Functions | |||
Array formula expertise needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions |