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.

 

[kweaver]

Welcome to Mr. Excel,

Please try to use XL2BB to illustrate your data.

Here's one say: <code>=FILTER(Original!A2:A8,INDEX(Original!B2:L8,,MATCH(TRUE,B2=Original!B1:L1,0))<>"")</code>

Change the ranges accordingly.

 

[kweaver]

here's another option:

=FILTER(Original!A2:A8,0<>FILTER(Original!B2:L8,B2=Original!B1:L1))

 

[Skiier89]

Thank you very much Kweaver! The nested filter function worked well. Another issue was also doing this within a defined table, which I have now abandoned as these functions are not supported with Excel tables..

Building on this - do you know how I could do the same thing, but if I wanted to show all names from Col A (the original sheet) that have a greater than 0 value in any dates that are less than or equal to the input date on sheet 2?

I've tried boolean logic for the second filter function - the "include" argument - by using the "<=" operators referencing the input date on sheet 2: =FILTER(Original!A2:A8,0<>FILTER(Original!B2:L8,B2<=Original!B1:L1))

However I keep getting a #calc or #value error. Do I need to nest yet another filter function?

For instance, if "12/31/2022" was the input date on sheet 2, all names from Col A would return since all names have a > 0 value for the dates equal to or preceding 12/31/2022.

Thanks again

 

[kweaver]

Not sure how elegant this is, but I think it works. I'll have to see if there's a shorter version.

=FILTER(Original!A2:A8,TRANSPOSE(0<>MMULT(--(Date!B2>=Original!B1:L1),--TRANSPOSE(Original!B2:L8>0))))

 

[Skiier89]

Not sure how elegant this is, but I think it works. I'll have to see if there's a shorter version.

=FILTER(Original!A2:A8,TRANSPOSE(0<>MMULT(--(Date!B2>=Original!B1:L1),--TRANSPOSE(Original!B2:L8>0))))

Incredible. Absolutely incredible - thank you Kweaver!!

I am not familiar with MMULT but this works in my example workbook. Now I need to implement it into the actual workbook.

I would be remiss if I didn't ask you one more thing - how to make this filter work for in between two dates. For example - return all names that have a non-zero value for any dates between 12/31/17 and 12/31/18. Results would spill all names besides Sarah Wilson in the original example.

 

[kweaver]

MMULT means Matrix MULTiplication. It's a big more complicated mathematically than just simply product because it's multiplying rows by columns and then adding the results.

Between dates. Now you're pushing it...LOL...just kidding. Let me try that and see what i come up with.

How about this?

=FILTER(Original!A2:A8,TRANSPOSE(0<>MMULT(--(Date!B3>=Original!B1:L1)*(Date!B2<=Original!B1:L1),--TRANSPOSE(Original!B2:L8>0))))

 

[Fluff]

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.

 

[Skiier89]

Incredible. Absolutely incredible - thank you Kweaver!!

I am not familiar with MMULT but this works in my example workbook. Now I need to implement it into the actual workbook.

I would be remiss if I didn't ask you one more thing - how to make this filter work for in between two dates. For example - return all names that have a non-zero value for any dates between 12/31/17 and 12/31/18. Results would spill all names besides Sarah Wilson in the original example.

I manipulated the array criteria in MMULT to the following and this has worked:

=FILTER(Original!$A$2:$A$8,TRANSPOSE(0<>MMULT(--(Original!$B$1:$L$1<=Date!$B$4)*(Original!$B$1:$L$1>=(Date!$B$4-365)),--TRANSPOSE(Original!$B$2:$L$8>0))))

Where Date!B4 is the input date. So the resulting array in any date less than the input date (usually going to be the year end 12/31/xx) but greater than the input date minus 1 year (i.e. -365).

When implementing this in the actual data set, the filtered results are including names that have a 0 or blank value for the date/year in question, but I can't determine why.

 

[Fluff]

Did you try the formula I suggested?