Searching an array.

[Fusiontron]

I need to do the following:
I have a horizonal row of numbers X1, . . . , Xn.
I have a corresponding horizontal row of numbers Y1, . . . ,Yn.
I want to search the Y row to find the first value (going from left to right) less than some value. Then return the corresponding X row value.
I tried used the MATCH and HLOOKUP functions, nothing seems to work because I am an Excel n00b.

[Ron Coderre]

Try a structure something like this:

Please Login or Register  to view this content.

Is that something you can work with?

[Fusiontron]

Ok so I probably need to show an example.

If I have this row: 0 1 2 3 4 5 6 7 8 9 10
And this row below it: 25 20 15 10 5 0 -5 -10 -15 -20 -25
And I want to find the first value in the second row less than -18 and then return the corresponding value in the first row which would be 9. But I will give your code a try.

[Fusiontron]

Well I understand the INDEX part but not MATCH. I use the value 1 to indicate less than but it always comes up with the last column.

EDIT: With the last column in MATCH as -1 I get the correct answer. I will check for errors but problem is most likely solved.

[Ron Coderre]

OK...
With your sample data in A1:K2
this regular formula returns the first A1:K1 value where the corresponding Row_2 value is less than -18:

Please Login or Register  to view this content.

Is that something you can work with?