Returning a text value from an array

Question

I have a large spreadsheet that has names in column a, dates in column b, positions in column c, and then columns of test results in d-az. I can search through and find results the record holder for each test, and for each test based on position, but how do I get it to return the name of the person with the record?

I am getting close. I got this statement to work:

{=INDEX(DATABASE!$A:$AX,MATCH(MAX(IF(DATABASE!$AX:$AX=B146,DATABASE!$B:$B)),IF(DATABASE!$AX:$AX=B146,DATABASE!$B:$B),0),1)}

Where AX is the column with the score I am trying to match to B146

And B is the data that I want the most recent data of

But I am getting an error with the following:

{=INDEX(DATABASE!$A:$AW,MATCH(MAX(IF(AND(DATABASE!$C:$C="L",DATABASE!$AW:$AW=B145),DATABASE!$B:$B)),IF(AND(DATABASE!$C:$C="L",DATABASE!$AW:$AW=B145),DATABASE!$B:$B),0),1)}

Where AX is the column with the score I am trying to match to B146

And B is the data that I want the most recent data of

And C is the position column that I want to match.

Any ideas?

sergeibaklan · Answer

Hi Jerome,&nbsp;In general not necessary to use IF and AND here. Each your condition returns array from&nbsp;TRUE and FALSE, other words array from&nbsp;1 and 0. Multiplication of all your conditions give combined array for all conditions which filter the column you'd like to return.&nbsp;First you formula (array one) could be re-written as=INDEX(Database!$A:$AX,
      MATCH(
         MAX((Database!$AX:$AX=B146)*Database!$B:$B),
         (Database!$AX:$AX=B146)*Database!$B:$B,
      0),1)which returns the cell in the column A in the row there the date (column B) is maximum for all rows where the score (column AX) is equal to B146.&nbsp;To above you may as many criteria as you need using multiplication. To filter additionally by column C we may use (also as array formula)=INDEX(Database!$A:$AX,
      MATCH(
         MAX((Database!$AX:$AX=B146)*(DATABASE!$C:$C="L")*Database!$B:$B),
         (Database!$AX:$AX=B146)*(DATABASE!$C:$C="L")*Database!$B:$B,
      0),1)
   And INDEX could be on $A:$A&nbsp;The only difference with your variant if no combination is found your formula returns the error, and modified formula returns the cell in first row. Usually in first row are column names, could be not critical, otherwise&nbsp;another modification is required.&nbsp;&nbsp;&nbsp;

deleted · Answer

Thank you for the help. You got me on the right track. It didn't match exactly, but the final solution that worked was:

=INDEX(DATABASE!$A:$A,MATCH(1,(DATABASE!$B:$B=MAX(IF((DATABASE!$AW:$AW=B145)*(DATABASE!$C:$C="L"),(DATABASE!$B:$B))))*(DATABASE!$AW:$AW=B145)*(DATABASE!$C:$C="L"),0))

Thank you again!!

sergeibaklan · Answer

Hi Jerome,&nbsp;Glad to know you sorted this out. Just in case, i compared both formulas (only changed in my AX on AW), they give the same result - see attached.

Forum Discussion

Returning a text value from an array

3 Replies

Resources