Array lookup returning value equal or next greater depending on criteria (column)

[FPS]

Trying to determine future value based on given value (need to return the future value and "STEP")

Lookup array of future value example: A1:E6

STEP CAT1 CAT2 CAT3 CAT4
1 475.66 586.33 956.48 962.11
2 484.14 594.21 957.15 968.18
3 494.78 595.89 958.79 979.01
4 495.85 598.10 967.46 984.23
5 502.59 599.45 985.22 998.36


Lookup value: I need these values returned:
Category Value F.Value STEP
CAT1 483.15 494.78 3 - to lookup in CAT1 column only
CAT3 954.66 956.48 1 - to lookup in CAT3 column only
CAT4 983.56 984.23 4 - to lookup in CAT4 column only

Help much much appreciated!

[FPS]

Sample file attached...

[tigeravatar]

FPS,

Attached is a modified version of your sample workbook.
In cell L2 and copied down is this formula:

Please Login or Register  to view this content.

In cell M2 and copied down is this formula:

Please Login or Register  to view this content.

[FPS]

Awesome! That worked PERFECTLY! Thanks!

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[FPS]

Addendum - the formula wasn't handling returned values for TOP row of lookup correctly. For example, if a future value should be CAT1, the returned value was CAT2 (since looking for the next greater value I guess.. the "+1" at the end of the formula.. if I removed the "+1" my returned values on other CATs were wrong. I adjusted by adding a row of 0's at the top of my lookup array - not elegant but it worked.

I tried to add an IF stmt to check the current value against CAT1 value but messed up the forumula.. LOL

I am satisfied with what I have. Thanks again tigeravatar!