Indirect formula to find MIN of matching range

0
votes

I have a spreadsheet with two sheets, first sheet contains Name (col:C), and Finish time (col:G), second sheet I have a column also called Name (col:A) , and a column called Personal Best Time (col:G).

Currently, in Sheet2:Col:G, i'm using a formula to search Sheet1 for the lowest Finish time value relating to that particular row's Name column (as below):

=ArrayFormula( min( if('Sheet1'!C:C = A2, 'Sheet1'!G:G) ) )

But for every row in Sheet2, this formula needs to be copied, with the only difference being the A2 reference (which is referring to the Name column of the current row).

So my hope was to create an indirect formula, where the specific range is entered in the header column, and automatically applied to each row. So this is what I have so far:

=ArrayFormula( IF( ISBLANK(A:A), "", IF( ROW(G:G)=1, "Personal Best Time", ArrayFormula( min( if( 'Sheet1'!C:C = A2, 'Sheet1'!G:G ) ) ) )))

But how do I make it so that A2 changes depending on the row number?

I tried A:A, but if I'm right, that would be attempting to compare the whole Name column from Sheet2.

1
google-sheets
Sorry, I'm new to formulas, another user edited my post and pointed me in the direction of indirect. And I figured most [excel-formula] would be the same as Google Spreadsheet – 92jacko

1 Answers

1
votes

Does this formula work as you want:

=ArrayFormula( IF( ISBLANK(A:A), "", IF( ROW(A:A)=1, "Personal Best Time", ArrayFormula(Vlookup(A:A,SORT({Sheet1!C:C,Sheet1!G:G},2,1),2,0)))))