Vlookup and if condition

Ashti

New Member
Joined
Jul 8, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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:
PeriodJuly_Week2
NameDate
sheet2 as which has below data
PeriodNameDatePresent_Absent
July_Week1Name1
01/07/2020​
Absent
July_Week2Name2
06/07/2020​
Absent
July_Week2Name3
06/07/2020​
Absent
July_Week2Name4
07/07/2020​
Absent
September_Week2Name5
10/09/2020​
Absent
July_Week2Name6
08/07/2020​
Absent
August_Week2Name7
06/08/2020​
Absent
July_Week1Name8
02/07/2020​
Absent
July_Week1Name9
01/07/2020​
Absent
July_Week2Name10
08/07/2020​
Absent
August_Week3Name11
12/08/2020​
Absent
September_Week2Name12
09/09/2020​
Absent
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
I suppose you want to return in Period July_Week 1, Names which are absent along with the dates.

3RD QUARTER PAN CORRECTION.xlsx
ABCDEFGHIJ
2Period
3July_Week2AbsentPeriodNameDatePresent_Absent
4July_Week2Name206/07/2020AbsentJuly_Week1Name101/07/2020Absent
5July_Week2Name306/07/2020AbsentJuly_Week2Name206/07/2020Absent
6July_Week2Name407/07/2020AbsentJuly_Week2Name306/07/2020Absent
7July_Week2Name608/07/2020AbsentJuly_Week2Name407/07/2020Absent
8July_Week2Name1008/07/2020AbsentSeptember_Week2Name510/09/2020Absent
9July_Week2Name608/07/2020Absent
10August_Week2Name706/08/2020Absent
11July_Week1Name802/07/2020Absent
12July_Week1Name901/07/2020Absent
13July_Week2Name1008/07/2020Absent
14August_Week3Name1112/08/2020Absent
15September_Week2Name1209/09/2020Absent
16
17
18
19
Sheet2
Cell Formulas
RangeFormula
A3A3="July_Week2"&J4
B4:E8B4=FILTER(G4:J15,G4:G15&J4:J15=A3)
Dynamic array formulas.
 

Ashti

New Member
Joined
Jul 8, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I do not have filter function using microsoft 2010
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
But you have updated office 365 in your profile.
Anyways is the result what you are looking for???
 

Ashti

New Member
Joined
Jul 8, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Ashti

New Member
Joined
Jul 8, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

3RD QUARTER PAN CORRECTION.xlsx
ABCDEFGHIJ
2Period
3July_Week1AbsentDate RevisedPeriodNameDatePresent_Absent
4NameDate01/07/2020July_Week1Name101/07/2020Absent
5Name901/07/202006/07/2020July_Week2Name206/07/2020Absent
6Name802/07/202006/07/2020July_Week2Name306/07/2020Absent
7Name101/07/202007/07/2020July_Week2Name407/07/2020Absent
810/09/2020September_Week2Name510/09/2020Absent
908/07/2020July_Week2Name608/07/2020Absent
1006/08/2020August_Week2Name706/08/2020Absent
1102/07/2020July_Week1Name802/07/2020Absent
1201/07/2020July_Week1Name901/07/2020Absent
1308/07/2020July_Week2Name1008/07/2020Absent
1412/08/2020August_Week3Name1112/08/2020Absent
1509/09/2020September_Week2Name1209/09/2020Absent
16
17
Sheet2
Cell Formulas
RangeFormula
A3A3="July_Week1"&J4
B5:B104B5=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:C104C5=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
NameRefers ToCells
_FilterDatabase=Sheet2!$A$4:$J$104B5:C5


Press Ctrl+Shift+Enter and fill the ranges
 

Ashti

New Member
Joined
Jul 8, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
How can I download your excel sheet?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
Just copy the sheet (Option available just above row 2) and paste it to your excel
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,515
Members
417,149
Latest member
drbro

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
Top