Search code examples
excelexcel-formula

Excel: Print exact name found from the list for exact date

Let's say I have a table with dates (all the calendar dates for 2024) and names (divided into columns):

AllNames

Date Names
01 Jan Aaa Bbb Vvv
02 Jan Ccc
03 Jan Ddd

I also have a list of some employee' names (current employees, names can be changed in future):

employees

Employees
Aaa
Vvv
Ccc

I would like to check if employee from the sheet Employees ("Aaa" for example) is in the AllNames sheet/table for an exact date. And if so, then print their name (or multiple names if several employees found for that date) near to that date:

calendar

Date Math?
01 Jan Aaa, Vvv
02 Jan Ccc
03 Jan -

Format of columns, sheets and rows is not important.

=IF(COUNTIF(AllNames!2:2,Employees!A:A)>0,"Yes","No")

I tried this and it shows if a name is found from AllNames table, but that's my top of idea that did not come further.

Solution

  • You could try using the following formula:

    enter image description here

    =MAP(A1:A34,LAMBDA(b,
 LET(a, TOCOL(Employees!A:A,1),
 TEXTJOIN(", ",1,IF(FILTER(AllNames!B2:G7,AllNames!A2:A7=b,"")=a,a,"")))))

    But it is better to use Structured References aka Tables here instead of using the whole array. Therefore convert the each sources in sheets AllNames and Employees to tables.

    =MAP(A1:A34,LAMBDA(b,
 LET(a, TOCOL(Employees,1),
 TEXTJOIN(", ",1,IF(FILTER(DROP(AllNames,,1),TAKE(AllNames,,1)=b,"")=a,a,"")))))

    And if you don't have access to LAMBDA() helper functions could use the following:

    =IFERROR(TEXTJOIN(", ",1,XLOOKUP(INDEX(AllNames[[Name1]:[Name6]],MATCH(Calendar!A1,AllNames[Date],0),),Employees[Emp],Employees[Emp],"")),"")