Google Sheets - What is the formula that allows me to have search criteria instead of search_key for LOOKUP

0
votes

I am looking for a formula that I could use in a google sheet. I am looking for a first cell in a column that is negative value.

I thought LOOKUP was perfect for this task. It lets me search for a cell in a range and return the result from different column with same row that formula found.

However, I realized that search_key parameter for this formula has to be solid entry.

Is there any formula in Google Sheet that allows me to search the way I want?

To illustrate, I would like to automatically detect the following image's orange cell.

enter image description here

1
google-sheetsgoogle-sheets-formula

1 Answers

2
votes

Use Index function with Match function. Check if the value is less than 0 and return its index.

Here is the formula:

INDEX(A2:A6,MATCH(TRUE,INDEX(B2:B6<0,0,1),0))

Where 'A2:A6' is the month cell range and 'B2:B6' is the value cell range. Check this google spreadsheet I've created.

Reference: