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.

=IFERROR(INDEX(Book_Data!$D$2:$D$17,MATCH(A2,Book_Data!$C$2:$C$17,0)),"n/a")

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0
AndreamaryAuthor Commented:
Hi Barry,

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

Andrea
Index-Match-Sample.xls
0
byundtMechanical EngineerCommented:
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:
=Book_Data!$A$2:INDEX(Book_Data!$E$2:$E$65536,COUNTA(Book_Data!$A$2:$A$65536))

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.
Index-Match-SampleQ28225256.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Excellent! Thanks so much for the quick turnaround on this, too!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.