Ignore Blanks for INDEX/MATCH with multiple criteria search

Hello All,

I have searched online for this solution but I cant seem to get any formulas to work for my example. I have been using INDEX & MATCH in excel to pull data from a table using a row and column heading. The formula I use is:-

=INDEX($B$5:$K$8,MATCH($A14,$A$5:$A$8,0),MATCH(B$13,$B$4:$J$4,0))

This looks up the table B5:K8 and matches A14 with the row and B13 with the column. The formula then pulls back the data from the table for these two criteria. However I am not sure how to get the formula to ignore blanks. The column headings repeat on occasion and sometimes the data is missing from the first column and is contained in the second column or third column, the formula only looks up the first matching column and returns the value. I am not sure how to get it to ignore this cell if it finds a blank. Its hard to describe this in great detail without making it sound too complicated. I have attached a sample workbook to demonstrate what I mean

Index Match example.xlsx

Please note the data can sometimes be between 10-100 columns long and 1-50 rows in length. The workbook is just a simplified version of what I mean.

Thanks
Stephen

Could be like this?

Thank you Azumi. This is exactly what I needed and I was able to adapt this for a much larger table.

Azumi,
Thanks for solving my initial question. Is there a way to put this formula into Visual Basic so that it does the same thing?
I am working with a larger array and the calculation time takes a minute or two. I was wondering if Visual Basic would speed this up?
Thanks
Stephen