Need a function assist

New Contributor


I have been using Excel for a number of years now and have some knowledge using the just basic formulas of Excel on a day to day, which allows me to use Excel efficiently for my job. I believe what I need help with today is a more advanced function that may be out of my scope of knowledge. I appreciate any and all help from the community. I don't wish to share the file at this time so I hope I can explain the parameters below in an easy-to-understand manner:


There are 400 cells with unique data in each that make up a section of one column, Column H. I need a function that searches the entire group of 400 cells in this column. When the first cell that is equal to or less than zero, I need the function to take that rows corresponding value in Column B and return that number. Column B will be numbered 0 through 400. 

This returned number will display in a separate sheet to tell me where the data (and therefore the row that it is in) is first equal to or less than zero without having to scroll through 400 rows of data to see which one fits the criteria. The rows that are labelled 0 through 400 are not in Excel Row order, i.e. the cell with number 1 in it will not be in Excel Row 1. Also, this needs to be an adaptive formula as the Value returned will be different with each new input that triggers it. 


I tried accomplishing this task with the "IFS" function, but unfortunately that only allows for 127 arguments. If you know of a function that can accomplish this and are willing to share, I would appreciate it greatly.


The file attached is a visual representation of the problem. Again, thank you for any help that you can provide! I hope this can be done and I hope I explained my predicament well enough! 

2 Replies
best response confirmed by SpartanMikeyD343 (New Contributor)


@Detlef Lewin you rock man thank you! Definitely was out of my scope of have yourself an awesome day!