Vlookup and if condition

[Ashti]

I have two sheets:
I am trying to do a vlookup for the period selected which is July_week2 using a condition (present_absent =Absent) and fetch name and date from sheet2
The vlookup keeps repeating the first name it gets and doesnt continue down further
output:
Name2 06/07/2020
Name2 06/07/2020

It should be
Name2 06/07/2020
name3 06/07/2020
Formula : IF(VLOOKUP($D$7,Sheet3!A9:H1659,6,0)="Absent",VLOOKUP($D$7,Sheet3!A9:H1659,2,0))

How do I fix this?

Sheet1:

Period	July_Week2
Name	Date

sheet2 as which has below data

Period	Name	Date	Present_Absent
July_Week1	Name1	01/07/2020​	Absent
July_Week2	Name2	06/07/2020​	Absent
July_Week2	Name3	06/07/2020​	Absent
July_Week2	Name4	07/07/2020​	Absent
September_Week2	Name5	10/09/2020​	Absent
July_Week2	Name6	08/07/2020​	Absent
August_Week2	Name7	06/08/2020​	Absent
July_Week1	Name8	02/07/2020​	Absent
July_Week1	Name9	01/07/2020​	Absent
July_Week2	Name10	08/07/2020​	Absent
August_Week3	Name11	12/08/2020​	Absent
September_Week2	Name12	09/09/2020​	Absent

 

[CA_Punit]

I suppose you want to return in Period July_Week 1, Names which are absent along with the dates.

3RD QUARTER PAN CORRECTION.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Period
3	July_Week2Absent						Period	Name	Date	Present_Absent
4		July_Week2	Name2	06/07/2020	Absent		July_Week1	Name1	01/07/2020	Absent
5		July_Week2	Name3	06/07/2020	Absent		July_Week2	Name2	06/07/2020	Absent
6		July_Week2	Name4	07/07/2020	Absent		July_Week2	Name3	06/07/2020	Absent
7		July_Week2	Name6	08/07/2020	Absent		July_Week2	Name4	07/07/2020	Absent
8		July_Week2	Name10	08/07/2020	Absent		September_Week2	Name5	10/09/2020	Absent
9							July_Week2	Name6	08/07/2020	Absent
10							August_Week2	Name7	06/08/2020	Absent
11							July_Week1	Name8	02/07/2020	Absent
12							July_Week1	Name9	01/07/2020	Absent
13							July_Week2	Name10	08/07/2020	Absent
14							August_Week3	Name11	12/08/2020	Absent
15							September_Week2	Name12	09/09/2020	Absent
16
17
18
19
Sheet2

Cell Formulas
Range	Formula
A3	A3	="July_Week2"&J4
B4:E8	B4	=FILTER(G4:J15,G4:G15&J4:J15=A3)
Dynamic array formulas.

 

[Ashti]

I do not have filter function using microsoft 2010

 

[CA_Punit]

But you have updated office 365 in your profile.
Anyways is the result what you are looking for???

 

[Ashti]

When we select the period : for ex : July_Week1/July_Week1,it should fetch the names and the dates of the absentees

 

[Ashti]

When we select the period : for ex : July_Week1/July_Week1,it should fetch the names and the dates of the absentees in the first sheet.
The second is just a sample i have provided here and is populated from various formulas so it cant be integrated to Sheet1...So I guess we have to use Vlookup only

 

[CA_Punit]

3RD QUARTER PAN CORRECTION.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Period
3	July_Week1Absent					Date Revised	Period	Name	Date	Present_Absent
4		Name	Date			01/07/2020	July_Week1	Name1	01/07/2020	Absent
5		Name9	01/07/2020			06/07/2020	July_Week2	Name2	06/07/2020	Absent
6		Name8	02/07/2020			06/07/2020	July_Week2	Name3	06/07/2020	Absent
7		Name1	01/07/2020			07/07/2020	July_Week2	Name4	07/07/2020	Absent
8						10/09/2020	September_Week2	Name5	10/09/2020	Absent
9						08/07/2020	July_Week2	Name6	08/07/2020	Absent
10						06/08/2020	August_Week2	Name7	06/08/2020	Absent
11						02/07/2020	July_Week1	Name8	02/07/2020	Absent
12						01/07/2020	July_Week1	Name9	01/07/2020	Absent
13						08/07/2020	July_Week2	Name10	08/07/2020	Absent
14						12/08/2020	August_Week3	Name11	12/08/2020	Absent
15						09/09/2020	September_Week2	Name12	09/09/2020	Absent
16
17
Sheet2

Cell Formulas
Range	Formula
A3	A3	="July_Week1"&J4
B5:B104	B5	=IFERROR(INDEX(H4:H15,IFERROR(LARGE(IF(--IF(G4:G15&J4:J15=A3,F4:F15)>1,ROW(INDIRECT("A1:A"&COUNTA(F4:F15)))),ROW(A1:A100)),"")),"")
C5:C104	C5	=IFERROR(INDEX(I4:I15,IFERROR(LARGE(IF(--IF(G4:G15&J4:J15=A3,F4:F15)>1,ROW(INDIRECT("A1:A"&COUNTA(F4:F15)))),ROW(A1:A100)),"")),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$4:$J$104	B5:C5

Press Ctrl+Shift+Enter and fill the ranges

 

[Ashti]

How can I download your excel sheet?

 

[CA_Punit]

Just copy the sheet (Option available just above row 2) and paste it to your excel

 

[Ashti]

Thank you...