General discussion

Locked

Excel 97: Find a Value Less Than Another in Array

By Graham Bennett ·
I have a two column array of 30 rows.

Each cell in Column A contains a sequential value from 61 - 90 (person's age).

Each cell in column B contains a decreasing value that eventually goes negative (retirement savings being run down). The starting value in the first row of column B can vary, which means that the cell in Column B that goes negative can change.

I want to be able to find the value for the cell in Column A alongside the same row that the cell in column B goes negative.

I have attempted to use VLOOKUP but seem unable to use a 'less than zero' value.

Your assistance will be appreciated.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Excel 97: Find a Value Le ...

Array enter this formula. (type in the formula and then hit Ctrl+Shift+Enter. The brackets can not be entered from the keyboard.

{=INDEX(A2:A32,MATCH(1,IF(B2:B32<0,1,),0))}

Collapse -

by Graham Bennett In reply to

Thank you. This has helped me although I am still to make it work. I am getting a #N/A from "MATCH(1,IF(B2:B32<0,1,),0)" portion. I am looking at the functions individually to see how to progress your suggestion.

Collapse -

by DKlippert In reply to Excel 97: Find a Value Le ...

Make sure you use Ctrl+Shift+Enter.
It is an Array formula

Collapse -

by Graham Bennett In reply to

Thanks Doug, that did the trick. I haven't come across the ctrl+shift+enter before.

Regards

Graham Bennett

Collapse -

by Graham Bennett In reply to Excel 97: Find a Value Le ...

This question was closed by the author

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums