Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match - #N/A
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? |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
I've confirmed that the formula is correct. Would you like me to send
you a sample file? In article . com, wrote: 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. |
#5
|
|||
|
|||
Try this: =CONCATENATE(IF(SUMPRODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))<=26,CHAR(SUMPRODU CT(--(C22:AP34=C20)*COLUMN(C22:AP34))+64),"A"&CHAR(SUMP RODUCT(--(C22:AP34=C20)*COLUMN(C22:AP34))+64-26)),SUMPRODUCT(--(C22:AP34=C20)*ROW(C22:AP34))) Hope it helps. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=397291 |
#6
|
|||
|
|||
Sure - is best for this.
Thanks |
#7
|
|||
|
|||
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))) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=397291 |
#8
|
|||
|
|||
I've just emailed you a sample file. Post back if you have any
questions or need further help. In article .com, wrote: Sure - is best for this. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Match Function arguments | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |