i have a question about formula. I am using 1 workbook as template for extracting data from another workbooks.
This picture show template workbook:
In the formula there are 2 ref values, ref value marked with number 1 is for getting row, and second ref value marked as 2 on picture is for getting column number.
This is workbook from which i am pulling out data:
=MATCH(P2;INDIRECT("A"&MATCH(OFFSET(F3;0;-5);INDIRECT("["&$AC$12&"]Sheet1!$A:$A"))+ROW(INDIRECT("["&$AC$12&"]Sheet1!$A:$A"))-1&":"&"O"&MATCH(OFFSET(F3;0;-5);INDIRECT("["&$AC$12&"]Sheet1!$A:$A"))))
Formula is giving correct row number: 2 but it is returning wrong column number: 11 but it should return for column number:7 since ref value 2 (20.6.2021.) is in 7th column in Book2 workbook.
EDIT: result Result should be: in this picture cell I3 contains formula which works something like lookup, and it is returning value based on row and column number. In my example, in first picture 1st ref value from cell A3 is lookuped in Book2.xlsx and ROW which will be returned is number 2, if there is matching number then it should lookup 2nd ref value which is: 20.6.2021. from cell P2. Since there is matching date in ROW 2, final formula as result should return that date: 20.6.2021.
Can somebody help me, i dont know where could be the problem?
Indirect
is the best approach here. – cybernetic.nomad