Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 441
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 441
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 441
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 441
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 441
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula Help Needed nelly Excel Worksheet Functions 5 April 13th 10 12:43 PM
Answers needed for challenging formula Sum Limit and marking Excel Worksheet Functions 16 April 17th 06 12:15 PM
Array formula expertise needed CJ-22 Excel Worksheet Functions 0 February 10th 06 03:56 AM
Array formula needed ZipCurs Excel Worksheet Functions 4 December 17th 05 03:16 PM
Array formula needed Domenic Excel Worksheet Functions 0 August 26th 05 04:23 AM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"