Forum Discussion
Index function with Indirect function within
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))
yes i think so. The end result should look like this:
Thank you for your help btw, much appreciated
Place in N5 and fill across:
=INDEX(Table1,MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0),MATCH(N4,Table1[#Headers],0))
- omg thank you broski if you wouldnt mind could you explain how this works, so next time I can do it on my own? Again youre a king thanks
Step through the INDEX arguments:
array: Where will your return come from? We give it Table1 because your return might come from one of 3 columns depending on the MATCH results.
Row_num: MATCH(MAX(Table1[[Gross]:[Gross]]),Table1[[Gross]:[Gross]],0)
The MAX obtains the maximum value from the Gross column of the table. Reference to the Gross column appears as Table1[[Gross]:[Gross]] so the column names do not change when you drag the fill handle right.
MATCH obtains the position of the MAX result within the Gross column. That's your row number.Col_num: MATCH(N4,Table1[#Headers],0)
Here MATCH is obtaining the Column number where the results will come from.
Essentially, with this formula you're obtaining the Row and Column intersection of the table.