Locating data in a table

To find closest match to 1000 without going over:

=MAX(IF(E2:E5000<=1000,E2:E5000))

To extract data to the left:

=INDEX(A2:A5000,MATCH($H$1,$E$2:$E$5000)) copied to the right, where H1 contains the previous formula result. You can also replace H1 with that formula (without the = sign)

Originally Posted by engrexcel

I couldn't get that to work. I'm trying to search column 5 for the number 1000. When I used the formula, it gave me a number that was around row 1000 and a value of 5231.

I did forget to mention that the Max(If()) formula is an array formula and therefore must be confirmed with CTRL+SHIFT+ENTER not just ENTER....

Go to that cell with the formula and hit F2..then Hold Ctrl+Shift keys and hit ENTER... you will see { } brackets appear and hopefully the correct result.

The Col_E values in your table are above and below 1000.
If you want the data from the row where the Col_E value is "closest" to
1000...even if it's larger than 1000.

This formula finds that row

F2: =MATCH(1,INDEX(--(ABS(1000-E2:E4501)=MIN(INDEX(ABS(1000-E2:E4501),0))),0),0)

And this formula returns the Col_A data from that row

G2: =INDEX(A2:A4501,$F2)

Copy that formula across through K2

BTW...these are the values from the row where the Col_E value is closest
to 1000:
G2: 11.213
H2: 178.838
I2: 0.263
J2: 0.055
K2: 999.985

Does that help?

Using my posted example....Maybe this?

This formula returns the row ref for the first instance where the series
crosses 1000...either from below or above 1000.

F2: =MATCH(1,IF(E2<=1000,INDEX((E2:E4500<=1000)*(E3:E4501>=1000),0),
INDEX((E2:E4500>=1000)*(E3:E4501<=1000),0)),0)

Does that work for you?

Originally Posted by engrexcel

Yes, that helped a ton! Thanks! Is there a way to get the formula so it grabs the data the first time 1000 is crossed? I'm not really concerned with any occurance of 1000 after it has been reached.

1.463 149.627 1.044 0.985 999.87

That was why I posted that set of numbers.

Try:

=INDEX(A$2:A$5000,MATCH(TRUE,$E$2:$E$5000>=1000,0)-1)

confirmed with CTRL+SHIFT+ENTER and copied down.

Can you post the revised formula?

Something like this should work.

=INDEX(A$2:A$5000,MATCH(TRUE,$E$2:$E$5000>=C5,0)-1)

That is quite different than the formula I had suggested for your initial question.

I'm not sure I know what you are trying to do with that formula...

You've got arrays of different sizes
You've got conditions >=J9 and <=J9 will both be true when the value in the range actually equals J9...
And your value if true is not much different than the value if false (which the range overlaps your condition check value (E18).

Please explain in words what you are actually wanting to accomplish.