Lookup to Return Values based on Adjacent Column Search

Skiier89

New Member
Joined
Jun 14, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.

1655237042413.png
1655237068400.png
 
How about
Fluff.xlsm
ABCDEFGHIJKLMNOP
131/12/201730/06/201815/09/201802/01/201915/04/201931/12/201906/05/202001/10/201001/01/202115/09/202131/12/2022
2John25505535415215/09/2018John
3Chris6510213533166891522010/01/2020Chris
4Pam581394687Pam
5Sarah181920454446Sarah
6Scott222222222222Scott
7Ant2Janet
8Janet222222
9
Sheet4
Cell Formulas
RangeFormula
P2:P7P2=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?
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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!
 
Upvote 0
As long as B1:L1 are real dates & not text, then it doesn't matter what date you use as the criteria.
Fluff.xlsm
ABCDEFGHIJKLMNOP
131/12/201730/06/201815/09/201802/01/201915/04/201931/12/201906/05/202001/10/202001/01/202115/09/202131/12/2022
2John25505535415231/12/2020Chris
3Chris65102135331668915220Pam
4Pam581394687Sarah
5Sarah181920454446Scott
6Scott222222222222
7Ant2
8Janet222222
9
Original
Cell Formulas
RangeFormula
P2:P5P2=FILTER(A2:A8,MMULT((B1:L1<=O2)*(B1:L1>=O2-365)*(B2:L8<>""),SEQUENCE(COLUMNS(B1:L1),,,0)))
Dynamic array formulas.
 
Upvote 0
I've gotten this to work perfectly in my workbook - thank you very much again for all your help!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top