Windows Forum

Ask something
Discussion Locked

Excel 97: Find a Value Less Than Another in Array

0 helpful
5 replies

by graham bennett

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

5 replies

by dklippert

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))}

by graham bennett

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.

This discussion is closed to new replies.