Lookup to Return Values based on Adjacent Column Search

[Skiier89]

Hello -

First time posting but long time Excel user. Here is my question -

I have a data set on Sheet 1. The rows in column A contain names. The headings in column B, C, D, and so on contain dates.

Each name in column A may or may not have a value for a given date in the subsequent columns.

On a separate sheet (Sheet 2) within the workbook, I input one of dates from the table (say "4/15/2019") in a cell. How can I structure a lookup (v-, h-, x-, index/match) to return (or even spill) all names in column A of the table that have a non-blank value in the column with the corresponding date "4/15/2019"? If I change the date to another date, I would like the lookup to essentially filter out values in column A of the table to only those with a non-zero value in the column corresponding to the date entered.

For example, the formula on sheet 2 should only return/spill names: John Smith, Chris Cooper, Pam Johnson, Scott Peters, Anthony White.
When the date on sheet 2 is changed from "4/15/2019" to "12/31/2022", the formula should return Chris Cooper, Pam Johnson, Sarah Wilson, Scott Peters, and Janet Pearson.

I cannot use macros or VBA.

I've read a little about boolean logic in conjunction with the filter or sort functions, but can't wrap my head around it.

 

[Skiier89]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		31/12/2017	30/06/2018	15/09/2018	02/01/2019	15/04/2019	31/12/2019	06/05/2020	01/10/2010	01/01/2021	15/09/2021	31/12/2022
2	John	25	50	55	35	41	52								15/09/2018	John
3	Chris		65	10	21	35	33	16	68	91	52	20			10/01/2020	Chris
4	Pam			5	8	1	3	9	4	6	8	7				Pam
5	Sarah						18	19	20	45	44	46				Sarah
6	Scott	22	22	2	2	2	2	2	2	2	2					Scott
7	Ant	2														Janet
8	Janet	2	2	2	2	2	2
9
Sheet4

Cell Formulas
Range	Formula
P2:P7	P2	=FILTER(A2:A8,MMULT((B1:L1>=O2)*(B1:L1<=O3)*(B2:L8>0),SEQUENCE(COLUMNS(B1:L1),,,0)))
Dynamic array formulas.

In future can you please post data, rather than an image.

I'm sorry - my fault for not following the guidelines about posting data and not images.

I don't think I need to post any more data, but I am getting stuck with using dates... maybe you have a suggestion?

The shorter formula that uses sequence() and columns() is working better than the one that used transpose(). It is returning the correct names in the actual data set I'm working with. However, the filter returns an error if I don't use a range with the exact dates in B1:L1. Ideally, I need to use 1/1/xx and 12/31/xx of the desired year. Using a validated list referencing B1:L1 works for start and end dates (formatted as dates). But as soon as I use "1/1/20xx" and "12/31/20xx" instead, I get a #CALC error, even though both the 1/1 start and 12/31 end dates are still formatted as dates. All dates in the range B1:L1 are formatted as dates, so I thought this would work..

Should I be using a Date() formula when referring to the start and end date parameters?

 

[Skiier89]

Did you try the formula I suggested?

Yes I did - the sequence and column formula is working now.

I know Excel is quite particular about dates, and I can't get 1/1 and 12/31 dates to work within the MMULT function. The filter returns #Calc error unless I use the exact dates in B1:L1 for the starting and ending parameter.

The beginning date is always going to be 1/1 of the current year. The ending date is always going to be 12/31 of the current year. But "1/1" and "12/31" for that year are not necessarily listed in the columns. For instance, the first date for 2014 is 1/5/2014. The last date for 2014 is 12/6/2014. But if I refer to a cell value with 1/1/2014 and 12/31/2014, the function is not able to find the array for 1/5/2014 - 12/6/2014.

I guess the easiest fix would be to simply add a 1/1 and 12/31 column for each year.. but if it's just a matter of modifying the greater than or less than parameters with a date() function of some sort, that would be awesome..

You & Kweaver have been extremely helpful and I can't thank you enough!

 

[Fluff]

As long as B1:L1 are real dates & not text, then it doesn't matter what date you use as the criteria.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		31/12/2017	30/06/2018	15/09/2018	02/01/2019	15/04/2019	31/12/2019	06/05/2020	01/10/2020	01/01/2021	15/09/2021	31/12/2022
2	John	25	50	55	35	41	52								31/12/2020	Chris
3	Chris		65	10	21	35	33	16	68	91	52	20				Pam
4	Pam			5	8	1	3	9	4	6	8	7				Sarah
5	Sarah						18	19	20	45	44	46				Scott
6	Scott	22	22	2	2	2	2	2	2	2	2
7	Ant	2
8	Janet	2	2	2	2	2	2
9
Original

Cell Formulas
Range	Formula
P2:P5	P2	=FILTER(A2:A8,MMULT((B1:L1<=O2)*(B1:L1>=O2-365)*(B2:L8<>""),SEQUENCE(COLUMNS(B1:L1),,,0)))
Dynamic array formulas.

 

[Skiier89]

I've gotten this to work perfectly in my workbook - thank you very much again for all your help!

 

[Fluff]

Glad we could help & thanks for the feedback.