Expanding index-match formula to search for first non-blank cell in column

I have an index-match formula that I need to change so that it goes to the first non-blank cell in the column and returns the result. Currently, the formula does not proceed beyond its first match, therefore sometimes returning "n/a" when, in fact, there is a value. I've attached a sample file demonstrating this. The first worksheet contains the book data, the second worksheet contains the results I'm getting using the formula below, and the third worksheet shows the results I want.


Who is Participating?
byundtConnect With a Mentor Commented:
I used the following array-entered formula:
=IFERROR(INDEX(Bookings,MIN(IF((INDEX(Bookings,,1)=$A2)*(INDEX(Bookings,,MATCH(B$1,Book_Data!$A$1:$E$1,0))<>""),ROW(Bookings)-ROW(Book_Data!$A$2)+1,"")),MATCH(B$1,Book_Data!$A$1:$E$1,0)) & "","")

To make the above formula work, I created a dynamic named range called Bookings that will grow with the amount of data you have. It has a Refers To formula of:

I also needed to have unique column header labels. So I changed column A to "BOOKINGS" on both worksheeets Book_Data and Desired_Results

To array-enter a formula:
1.  Click in the formula bar
2.  Hold Control and Shift keys down
3.  Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula

Formula is installed in cells E2:G5 of worksheet Desired_Results.
barry houdiniCommented:
Hello Andrea, there's no attachment here. Would you like to explain how it should work - you want a value returned even if A2 isn't found in Book_Data!$C$2:$C$17?

regards, barry
AndreamaryAuthor Commented:
Hi Barry,

Sorry about that. I've now attached the file!

AndreamaryAuthor Commented:
Excellent! Thanks so much for the quick turnaround on this, too!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.