Dynamic Table: INDIRECT and INDEX to compare cell content in array formula

Question

Overview:I am trying to use INDIRECT combined with INDEX, ROW and COLUMN to get a dynamic table working.

Problem:

1. For each cell of a given column in Worksheet1, read the content of the cell to its left.
2. Compare the content of a column from Worksheet2 to the read one .
3. For each match, get the value of a variable from Worksheet2 .
4. Evaluate the max of the found values, copy it into Worksheet1 original cell.

How I'm doing it (Wrong, apparently):

{=MAX(IF('Worksheet 2'!$A$4:$A$101=INDIRECT(ADDRESS(ROW();COLUMN()-1;4)); 'Worksheet 2'!$D$4:$D$101))}

What I would expect it to do:

1. INDIRECT(ADDRESS(ROW();COLUMN()-1;4)) --- Read content to the cell on the left in Worksheet 1

2. 'Worksheet 2'!$A$4:$A$101=INDIRECT(ADDRESS(ROW();COLUMN()-1;4)) --- Get an array of TRUE/FALSE values, where TRUE is found where Worksheet 2 column A contains the value previousl

3. IF('Worksheet 2'!$A$4:$A$101=INDIRECT(ADDRESS(ROW();COLUMN()-1;4)); 'Worksheet 2'!$D$4:$D$101) --- For each TRUE, extract the corresponding value from COLUMN D of Worksheet 2

4. {=MAX(IF('Worksheet 2'!$A$4:$A$101=INDIRECT(ADDRESS(ROW();COLUMN()-1;4)); 'Worksheet 2'!$D$4:$D$101))} --- From the COLUMN D extracted values, take the MAX

What I get: A #VALUE! Error.

Can somebody explain me why and propose a feasible substitution?

Answer 1

It's because your INDIRECT cell reference is part of an array formula, so you get {"A1"} instead of "A1", for example.

This can be resolved by using MAX, e.g.:

{=MAX(IF('Worksheet 2'!$A$4:$A$101=MAX(INDIRECT(ADDRESS(ROW();COLUMN()-1;4))); 'Worksheet 2'!$D$4:$D$101))}

(It looks like you also had a few brackets out of place.)

Update:

This is a better version which will work for your text columns (use MAX on the ROW and COLUMN functions):

=MAX(IF('Worksheet 2'!$A$4:$A$101=INDIRECT(ADDRESS(MAX(ROW());MAX(COLUMN())-1;4)); 'Worksheet 2'!$D$4:$D$101))