Index Match on row and column

Question

I am having a bit of trouble getting an index match formula to work.

My data table is in sheet3. In column one I have different items (like Fasso) and then I have values for each day in the remaining columns. E.g.

Date to Use     31/01/17    02/02/17    06/02/17
FASSO               0.00    12.99   0.00
FBLDR               8.68    8.57    0.00
FBSPB               7.78    8.01    0.00
FBSRB               9.18    9.07    0.00
FCGAR               9.52    9.97    0.00

On Sheet1 I have a drop down on item e.g. Fasso, and I select a date range whic creates a list of dates below. How can I pull through the correct data for that particular item and each of the dates?

Spreadsheet is file

Your question is not that clear but I'm guessing you should just lock ranges inside your formula like $A$1:$A$10 for example. — zipa

CallumDA CallumDA · Accepted Answer · 2017-02-16T13:50:51

From your attachment, I think you want this in Sheet3, cell B14

=INDEX(Sheet1!$D$3:$L$46,MATCH(Sheet3!$A$4,Sheet1!$A$3:$A$46,0),MATCH($A14,Sheet1!$D$2:$L$2,0))

Some of your dates don't exist in your table (in Sheet1) though, so they will show #N/A

Index Match on row and column

1 Answers