Multiple Parameters

Topic

New Reply

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.

Reply | Quote

Replies

An example would definitively be useful. I see something like:
=IF(AND(first condition >=, second condition <), INDEX(range, HLOOKUP(…), ROW()), False expresssion)

Reply | Quote

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.

Reply | Quote

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))

Reply | Quote

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.

Reply | Quote

How about:
=INDEX($G$3:$Q$12,MATCH(A2,$F$2:$F$12),B2+1)

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Makes perfect sense now. Thanks so much for all the great help!

Reply | Quote