Match - #N/A

[comish4lif]

Short question: What am I doing wrong?

I have a range of cells:

The range of cells (C22:ap34) is populated by the formula:
=INDIRECT(C$4&"!E"&$A22) thru =INDIRECT(AP$4&"!E"&$A34) - this created
a summary table pulling the values from other sheets as indicated by
the column header, always found in row 4.

In cell C20, I have the formula: =MAX(C22:AP34) - which shows me the
max value in that range.

In a cell, adjacent to the cell showing the max value, I want to show
the row and column header that matches that maximum value.

I have entered and INDEX/MATCH formula, but it will only result in a
"#N/A" error. I have tried it using the actual number instead of the
cell reference (C20) to the max value. I have tried to enter the match
formula on its own. I am using the ,0 parameter for an exact match, but
the same "#N/A" appears if I change it to ,1 or ,-1. Here's that exact
formula:

=INDEX(C21:AP21,MATCH(C20,C22:AP34,0))

The column headers are repeated on C21:AP21.

What am I doing wrong? Is the range too large - is it the formula that
populates the range?

 

[Domenic]

Assumptions:

C4:AP4 contains your column headers

B22:B34 contains your row headers


Formulas (all formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER):

For the row header, try...

=INDEX(B22:B34,MIN(IF((ISNUMBER(C22:AP34))*(C22:AP34=C20),ROW(C22:AP34)-R
OW(C22)+1)))

For the column header, try...

=INDEX(C4:AP4,MATCH(C20,INDEX(C22:AP34,MIN(IF((ISNUMBER(C22:AP34))*(C22:A
P34=C20),ROW(C22:AP34)-ROW(C22)+1)),0),0))

For the row number, relative to the first row in your sheet, try...

=MIN(IF((ISNUMBER(C22:AP34))*(C22:AP34=C20),ROW(C22:AP34)))

For the row number, relative to the first row in your table, try...

=MIN(IF((ISNUMBER(C22:AP34))*(C22:AP34=C20),ROW(C22:AP34)-ROW(C22)+1))

Note that if the maximum value contained in your table is 100, and cells
H28 and T23 contain 100, the formula will return the co-ordinates for
T23.

Hope this helps!

 

[comish4lif]

Thanks for the effort, but I am still getting a #N/A error for the
Column Header formula. And I did enter it as an array formula.

I didn't try the Row Header formula - it was easier to use Conditional
Formatting to highlight the Row that I am looking for.

Will the Match function work on it's own? I suspect that is the source
of the #N/A error.

 

[Domenic]

I've confirmed that the formula is correct. Would you like me to send
you a sample file?

 

[Morrigan]

Try this:

=CONCATENATE(IF(SUMPRODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))<=26,CHAR(SUMPRODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))+64),"A"&CHAR(SUMPRODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))+64-26)),SUMPRODUCT(--(C22:AP34=C20)*ROW(C22:AP34)))


Hope it helps

 

[comish4lif]

Sure - (e-mail address removed) is best for this.

Thanks

 

[Morrigan]

Sorry I misread your question, try this instead:

=CONCATENATE(OFFSET(A1,20,SUMPRODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))-1),SUMPRODUCT(--(C22:AP34=C20)*ROW(C22:AP34)))

 

[Domenic]

I've just emailed you a sample file. Post back if you have any
questions or need further help.