0

I have two excel tables in separate sheets:

table1:

Site_ID ....... Visit_Date
--------------------------
AS01            12-Feb-23
ES96            10-Jan-23   
GH15            14-Mar-23
AS01            26-Mar-23
VD10            08-Apr-23
LS18            14-Jan-23
GH15            26-Mar-23

table2:

            01-Feb-23   01-Mar-23   01-Apr-23
            28-Feb-23   31-Mar-23   30-Apr-23
Site ID         FEB23       MAR23       APR23
---------------------------------------------
AS01
ES96
GH15
VD10
LS18

I want to lookup for Site_ID in table1 and insert Visit_Date in table2 if this date is in proper range listed above table2 header.

I've tried using this formula:

=VLOOKUP(table2[@[Site ID]:[Site ID]],IF((table1[[Visit_Date]:[Visit_Date]]>=E$1)*(table1[[Visit_Date]:[Visit_Date]]<=E$2),table1[[Site_ID]:[Visit_Date]],""),12,FALSE)

but, unfortunately it returns dates that are not in range in FEB23 column and in rest of the columns it returns value error. What am I doing wrong?

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Mateusz Piskorski
  • 55
  • 6
  • Which version of Excel do you use? Do you have Excel-365? – Harun24hr Apr 10 '23 at 07:31
  • Yes, I have excel 365 – Mateusz Piskorski Apr 10 '23 at 07:32
  • Ok, it is not working. I thought that I have 365, but no. Unfortunately I have 2016 – Mateusz Piskorski Apr 10 '23 at 10:28
  • @MateuszPiskorski you can use this but it is limited to 2 outputs for a month, so if there are more dates for a specific site id you need to add the formula again. This is because you dont have `TEXTJOIN()` function. `=IFERROR(TEXT(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($B$2:$B$8)-1)/(($D5=$A$2:$A$8)*($B$2:$B$8>=E$2)*($B$2:$B$8<=E$3)),1)),"dd-mmm-yy;;"),"")&IFERROR(","&TEXT(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($B$2:$B$8)-1)/(($D5=$A$2:$A$8)*($B$2:$B$8>=E$2)*($B$2:$B$8<=E$3)),2)),"dd-mmm-yy;;"),"")` – Mayukh Bhattacharya Apr 10 '23 at 11:34
  • @MateuszPiskorski you have site ids more than once in a month. So `VLOOKUP()` function will not work neither any look up function( They will always return the first occurrence of duplicates). You need to use either as mentioned by Harun Sir or since you are not using the updated version than you can use the formulas as given above. – Mayukh Bhattacharya Apr 10 '23 at 11:41

1 Answers1

1

You may try the following formula-

=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$8,($A$2:$A$8=$E5)*($B$2:$B$8>=F$2)*($B$2:$B$8<=F$3),""),"dd-mmm-yyyy"))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36