Challenging long ARRAY formula needed - Can this be done?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 are:

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
 
=Index(Sheet1!$A$2:$A$500,Small(if((V1>=FL)*(V1<=FH)*(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
 
Wow! Thanks a lot Tom, I will give this a try.

Tom Ogilvy said:
=Index(Sheet1!$A$2:$A$500,Small(if((V1>=FL)*(V1<=FH)*(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
 
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!
 
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))
 
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!!!
 
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.
 
Thanks much for your post Frank. I will try your solution as well and keep
both versions documented in my library.

Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top