Hi - I have a data set containing dates in short date format from A62:U98 and the header to this set contains some names in A59:U59. Request you to please help me with a formula which returns the list of headers (A59:U59) if the data set matches a date input in V1. Thank you
File is also attached.
Hi Aayushi,
If you have Microsoft 365 you can use this formula:
=FILTER(A59:U59,BYCOL(IFERROR(SEARCH(V1,A62:U98),0),LAMBDA(array,SUM(array))))
Mynda
Thank you Mynda. Any other formula for other excel versions?
If you don't have dynamic arrays, then I'd do this with helper columns. My head would explode if I had to write a Ctrl+Shift+Enter array formula for that!
You can use SEARCH to find the matching dates for each column e.g.
=MAX(IFERROR(SEARCH($V$1,A62:A98),0))
Then use the result with IF to return the column label and then join them together with TEXTJOIN. See file attached.
Mynda
