Results 1 to 9 of 9
Thread: Multiple Parameters

20100219, 23:58 #1
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
I've tried everything I can think of and just can't figure this out. I have a numeric value that I need to compare to the value in 2 different columns. It must be >= the value in the first column and < the value in the second column. Once I've found that, I need to return a value in that same row, but from which column would be contingent on matching yet another value. Can anyone help with this....or does it even make sense? I can supply a snippet of data as needed.

20100220, 03:29 #2
 Join Date
 Dec 2009
 Location
 Mexico City, D.F., Mexico
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
An example would definitively be useful. I see something like:
=IF(AND(first condition >=, second condition <), INDEX(range, HLOOKUP(...), ROW()), False expresssion)This ecopost is made of recycled electrons

20100220, 10:07 #3
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Here's small sample of data. Bottom line is the Value in A3 has to be between the values in columns E and F, >= E and less than F. Once those cells/row is found, the value returned will be based on where a match is found to B2 looking at G2:Q2.
Since A3 = 247.75, the >= and < figures would be found in E12 and F12, so the row for the return value would be 12. The column the value is returned from will be based on where the value in B2 (in this case 3) is found in G2:Q2. Since 3is found in cell J2, the return value should be 45 from cell J12.
Hope this helps. I tried to apply the formula you entered and still couldn't make it work.

20100220, 10:32 #4
 Join Date
 Dec 2009
 Location
 Mexico City, D.F., Mexico
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
I now have a better understanding.
I will put in A3: =MATCH(A2,E2:E12,1)1
And in B3: =MATCH(B2,G2:Q2,0)
And in C3: =INDEX(G3:Q12,A3,B3)
Please let me know if it answers your question. If you want to test for #N/A, you will have to use an IF(ISNA(MATCH(B2,G2:Q2,0)),"no B match",MATCH(B2,G2:Q2,0))This ecopost is made of recycled electrons

20100220, 10:59 #5
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
It almost answers the question. I did what you said and it returns 44. From what I can disect in the formulas it looks like you checked if A2 were less than the values in column E, then based on where B2 was found in G2:Q2, you returned 44.
The thing is the value in A2 (247.75) has to be equal to or greater that a value in column E.....AND it must be less than the corresponding value in column F. In this case that would fall under row 12, so it should return 45. Sorry to be so thick, but for future reference, can you tell me what the 1 means at the end of the formuala =MATCH(A2,E2:E12,1)1? I'm close to understanding, but not quite there yet. Thanks for the patience.

20100220, 11:29 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about:
=INDEX($G$3:$Q$12,MATCH(A2,$F$2:$F$12),B2+1)
Steve

20100220, 12:29 #7
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Works perfectly, thanks! I would like to understand this so I can apply it to other situations. In the Match portion of the formula, if a match type is not specified, is the default the next higher number found? And in the B2+1 portion, why do you need +1?
Thanks again  I always get great info from loungers.

20100220, 14:08 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If no match type is specified it defaults to finding the value less than or equal to the lookup value. Thus in your example the "match row" found is for the value row with 140 in Col B. The formula adjusts for this by starting the index range 1 row down...
I used B2 +1 since the index starts with 1 and your columns start with a zero so the "index column" needed is 1 more than the value in B2
Steve

20100220, 14:13 #9
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Makes perfect sense now. Thanks so much for all the great help!