INDEX Max and Min

[josvill]

Hi There,

We are trying to not avail to get Index working for us.
We have a Row on the top. The formula works to identify those values
For the Column on the right side. The formula bring a value but it is not correct.
Something to do with the dynamic formula we suspect

This one does NOT work
{=INDEX(COLUMDESC,MAX(IF(TABLEDATA=B10,COLUMN(TABLEDATA)-MIN(COLUMN(TABLEDATA))+1)))}

This one works
{=INDEX(ROWDESC,MAX(IF(TABLEDATA=B10,COLUMN(TABLEDATA)-MIN(COLUMN(TABLEDATA))+1)))}


See attachment for more details

Best Rgds,
J

[CK76]

You are using COLUMN where ROW should be used.

=INDEX(COLUMDESC,MAX(IF(TABLEDATA=B10,ROW(TABLEDATA)-MIN(ROW(TABLEDATA))+1)))
Confirmed as Array (CSE).

[josvill]

Thanks CK76
When we introduce a new reference eg Q5, which is not in the list.
Our formula brings the first value from both list.
What can it be done to bring an error message that that product is not there?

[CK76]

One way.
=INDEX(ROWDESC,,IF(MAX(IF(TABLEDATA=B10,COLUMN(TABLEDATA)-MIN(COLUMN(TABLEDATA))+1))<>0,MAX(IF(TABLEDATA=B10,COLUMN(TABLEDATA)-MIN(COLUMN(TABLEDATA))+1)),""))

Confirmed as array.

Your issue occurs since Index(array,0,0) returns entire list. So you need to return non numeric value to return error for non existing values.

[CK76]

Alternate formula.
COLUMN Header Lookup:
=INDEX(ROWDESC,,SUMPRODUCT(COLUMN(TABLEDATA)*(TABLEDATA=B10))-1)

ROW Header Lookup:
=INDEX(COLUMDESC,SUMPRODUCT(ROW(TABLEDATA)*(TABLEDATA=B10))-1)

Both are non array formula.