Search first non-blank cell to return a value in another column

Hi All,

Want to look in one column and find the first non-blank numerical value, then have it return a value from another column.

Used to nest multiple IF functions together from different cells, but it seems overly complicated and time consuming. Sometimes I have over 30 cells to check.

For example, if Column A contains weekly sales data by week, entered weekly, and Column E has corresponding comparison data from the previous year. I want to enter a formula to check the first row that has sales data entered and have it match up the comparison value in the other column.

Any ideas would be appreciated.

Thank you!

Try this array formula

=INDEX(E1:E20,MATCH(TRUE,ISNUMBER(A1:A20),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Thanks for all the suggestions.

I have attached a test file showing what I'm trying to do.

I tried the index function but it didn't do what I was looking for.

Basically I would like B26 and C26 to automatically find the latest data in B4:B23 and C4:C23 and return the matching value in F4:F23 and G4:G23.

I update the data weekly in Column B and C.

Thanks again for all the prompt help. Really appreciated.

Edit: Excel version is very old '97

Try this in B26 and filled right to C26

=LOOKUP(9.99999999999999E+307,B4:F23)

Originally Posted by Jonmo1

Try this in B26 and filled right to C26

=LOOKUP(9.99999999999999E+307,B4:F23)

I don't know why or how this works, but it does! Thank you very much!

You're welcome.