How do I combine AVERAGEIFS with INDEX & MATCH?

Hello,

I have attached my example file with what I'm describing below.

In my "DATA" sheet:

1. There is the "Sample" column (Column A), the "Result" column (Column B) & the "Validity" column (Column C).

2. Using AVERAGEIFS, I'm able to get the average result from Column B if the sample number matches in column A & there are blanks in Column C, eg. < AVERAGEIFS(DATA!$B:$B,Data!$A:$A,"Sample",Data!$C:$C,"" >

Unfortunately, I often need to swap sheets between workbooks & sometimes these new sheets have the "Sample", "Result" & "Validity" on different columns.

I'm looking for a way for the formula to automatically know which columns to use in the AVERAGEIFS criteria array, based on the header, much like what INDEX+MATCH can do with individual results.

Is it possible?

In D3

Please Login or Register  to view this content.

Columns covered A:Z, Any Column can be any where in that range.
If required You can change the range by changing A:Z

Thank you, it works exactly as I hoped.
By changing the code slightly as below, it still works as yours but with a higher column range & no alphabets for the rows.

Please Login or Register  to view this content.

Originally Posted by byteroom

Thank you, it works exactly as I hoped.
By changing the code slightly as below, it still works as yours but with a higher column range & no alphabets for the rows.

Please Login or Register  to view this content.

I did not understand " with a higher column range & no alphabets for the rows"