# Windows

## General discussion

Locked

### Excel 97: Find a Value Less Than Another in Array

By ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -
by 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 -

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 In reply to Excel 97: Find a Value Le ...

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

Collapse -

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

Regards

Graham Bennett

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

This question was closed by the author

• 48

• 25

• 18

• 4