EXCEL to return first cell value if a match is found in an array

Hi

I have an array of 20 columns and 60 rows (increasing with time), each cell has either text or is blank.
I want to check if the value of the specific cell (A65 in my example) exist anywhere in this array (row by row)
IF the value exist then return the value of the 1st column (in my example date) and search value at the row where the match was found.
So result should be array also with 2 columns and xx rows.

In each row there can be only one specific value regardles of position (column).

I tried to use FILTER function but then I need to use sum operator (+) for each row,
and FILTER function returns complete row.
example: =FILTER(A1:Z60;(B1:B60=A65)+(C1:C60=A65)+(D1:D60=A65)+(E1:E60=A65)+(F1:F60=A65)+(G1:G60=A65)+(H1:H60=A65)+(I1:I60=A65)+(J1:J60=A65)+(K1:K60=A65)+(L1:L60=A65)+(M1:M60=A65)+(N1:N60=A65)+(O1:O60=A65)+(P1:P60=A65)+(R1:R60=A65);"empty")
(not all columns included)

I've tried count, match index, vlookups but nothing seems to really be working for me (or I don't know how to use them).
I don't want to use VBA.

Is there a simpler way to filter such a array?

Thank you for reading this especially if you are able to help me out on this.

Put this formula in cell AA1 on the PS2 sheet:

=COUNTIF($B$1:$Z1,$A$65)

Copy down to AA60. Then you can use this formula in cell A66:

=IF(ROWS($1:1)>MAX($AA:$AA),"",INDEX(A$1:A$60,MATCH(ROWS($1:1),$AA:$AA,0)))

and format as a date in the style you prefer. Put this very similar formula in B66:

=IF(ROWS($1:1)>MAX($AA:$AA),"",INDEX(B$1:B$60,MATCH(ROWS($1:1),$AA:$AA,0))&"")

and copy across to Z66. Then copy all the formulae from row 66 down as far as you think you need them.

Hope this helps.

Pete

Dear Fluff13,
thank you for your help.
that's almost what I thought,
but I don't need to retrive complete row after date,
only value of the cell A65 in this case.

In this example that would be
21.3.2020 T8
16.5.2020 T8
29.6.2020 T8
6.8.2020 T8
20.8.2020 T8

I appreciate your help with this.

Regards

A66

=IFERROR(INDEX($A$1:$A$60,AGGREGATE(15,6,ROW($A$1:$A$60)-ROW($A$1)+1/ISNUMBER(MATCH($B$1:$T$60,$A$65,0)),ROWS($A$1:A1))),"")

Copy down

Don't I get a mention?

Pete

As far as I see, the date values is unique, there's no entries more than once with same date.
So all I have to do just pull it's date which is somewhere in its columns contains value equal to input criteria
I converted your data and the result to Excel Named Ranged Table because it's ability to auto expand and store column formula.
Attached my file, you may try it if you wish.

Originally Posted by Glenn Kennedy

mp3lab... before going any further, please update your profile. It is obvious that you ar eno longer using Excel 2003.

Done

Originally Posted by Glenn Kennedy

Thanks!

maybe this:
=CHOOSE({1,2},FILTER(A1:A60,MMULT(--(B1:R60=A65),SEQUENCE(17,,,0))>0,"empty"),A65)

Dear Glenn Kennedy

That's exactly what I wanted, but in my Excel config formula is looking like this.

=CHOOSE({1\2};FILTER(A1:A60;MMULT(--(B1:Z60=A65);SEQUENCE(25;;;0))>0;"empty");A65)

Thanks for all your help.
I couldn’t have solve this without you.

Best Regards