Instead of INDIRECT() need to have INDEX and MATCH

Hello,
Can you help me
I do not want to use INDIRECT (). I want to use INDEX and MATCH.But i do not know how.
There is a link between bills.xlsx and evaluation.xlsx.
I need to manually change the matrix area in the file evaluation.xlsx (MatrixArea) monthly.
In the file Evaluations.xlsx I have to have the following results in the cells: cell C12 = 5, cell C13 = 7, cell C14 = 2 when I type in MatrixArea (A4 and D6)
In the file evaluation.xlsx, I have to get the following results in the cells: cell C12 = 2, cell C13 = 3, cell C14 = 9 when I type in MatrixArea (A11 and D13)

Config:
C12= VLOOKUP($A12;INDIRECT("'[bills.xlsx]2016'!"&MatrixArea!C6&":"&MatrixArea!D6&"");4;0)
C13= VLOOKUP($A13;INDIRECT("'[bills.xlsx]2016'!"&MatrixArea!C6&":"&MatrixArea!D6&"");4;0)
C14=VLOOKUP($A14;INDIRECT("'[bills.xlsx]2016'!"&MatrixArea!C6&":"&MatrixArea!D6&"");4;0)

bills.JPGevaluation MatrixArea.JPGevaulation -report.xlsx.JPG


Thanks

Unfortunately does not work.
There are two files bills.xlsx and evaluation.xlsx
The file bills.xlsx has a worksheet '2016'
The file evaluation.xlsx has two worksheets 'report' and 'MatrixArea'
In the Matrix area worksheet, I always want to specify the range manually on 'MatrixArea'. Corresponding to the entered matrix area, the counts should be transferred to the Worksheet 'report'

Thanks

Thanks a lot

I can not fully understand point 1. Can u tell me more about point 1.
I need to keep the files closed.
In the function of always changing the area it is a lot of work. For this reason I wanted to enter the matrix area dynamically in the worksheet 'MatrixArea'

I can not try it. because i get directly Error, when i try to paste the fuctions
=INDEX('MatrixArea'!$D$4:$D$6,MATCH(A12,'MatrixArea'!$A$4:$A$6,0)) in report!C12
or
=INDEX('2016'!$D$4:$D$6,MATCH(A12,'2016'!$A$4:$A$6,0)) in MatrixArea A4 or A6

Are you open to changing your method of determining the table?
It would be easier if you could just put the Month Name in a cell (instead of Cell Names).
Then we could use that month name to locate the table.

Is each table always equal in size?

Can you attach an actual book instead of pictures?

i try something
=INDEX('bills.xlsx]2016'!D4:D6;MATCH(A12;'bills.xlsx]2016'!A4:A6;0))
But in the next step I have to read out the matrix area from the matrix MatrixArea
So instead of D4: D6 I must then from the Worksheet MatrixArea! C6: D6, so I can always change this myself dynamically.
I Would be assigned to the sheet MatrixArea so that the matrix area is read out from the sheet matrix area.

Is that possible?
Thanks

I must keep the structure. I can not change that
I tried to attach the date but it is not possible
The admin has to allow me this.

Thanks

If you can't change the structure, then you can't change the formula.
INDIRECT is absolutely the only way to reference a range by typing it into a cell.
There is no other function (besides VBA) that can use the A4 and D6 written into those cells to create the range A4:D6

Can it be assumed that it is ALWAYS column A and D in those 2 cells?
i.e. we only really need to use the Row #s ?

INDIRECT is the only way.
I was thinking we could use the just the row #s if we can assume it's always column A and D.
But every method I've tried uses some function(s) that don't work on closed books.
I can't think of any method that works on closed books.

There is an Addin Called MoreFunc that has a similar indirect function that works on closed books.
Search Google for it, you'll probably have to sort through alot of junk and popups to find it.

Hi,

=INDEX(INDEX('[bills.xlsx]2016'!$D:$D;MatirxArea!$C$6):INDEX('[bills.xlsx]2016'!$D:$D;MatirxArea!$D$6);MATCH(A12;INDEX('[bills.xlsx]2016'!$A:$A;MatirxArea!$C$6):INDEX('[bills.xlsx]2016'!$A:$A;MatirxArea!$D$6);0))

The file bills.xlsx must be open. I would like to use the function with closed file ()bills.xlsx.
Do you have an idea?

You need full path to the file. But that should automatically update after you close the referenced workbook.

Ex: 'C:\Myfolder\Data\[bills.xlsx]2016'!$D:$D

I tried. It still does not work.

Thanks

Duplicate
https://www.excelforum.com/excel-for...and-match.html

The issue is not if index(match works on a closed book. Because it does.
The problem is the syntax of
INDEX(...):INDEX(...)

That specific setup does NOT work on closed books.

i can't upload the file. Admin does not allow this.

How did you make the list of months, so you can always select the current month from worksheets Months?

Thank you very much

it's perfect