vlookup with if function

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
Hello guys

I am trying to get the values from sheet2 to sheet1 using vlookup. I need help to add one if condition in the formula. If the day in Sheet1 cell B1 is Monday to Friday then it should get the value of Sheet2 cell B2, If the day in Sheet1 cell B1 is Saturday then it should get the value of Sheet2 cell C2 and finally If the day in Sheet1 cell B1 is Saturday then it should get the value of Sheet2 cell D2.

Query Vlookup+If.xlsx
ABCDE
1Mon-FriSatSun
2April100200300
3May200300400
4June300400500
5July400500600
6August500600700
7September600700800
8October700800900
9November8009001000
10December90010001100
11January100011001200
12February110012001300
13March120013001400
14
Sheet2


Query Vlookup+If.xlsx
ABCDEFGH
1April01-04-202002-04-202003-04-202004-04-202005-04-202006-04-202007-04-2020
2
3100
4
Sheet1
Cell Formulas
RangeFormula
B3B3=VLOOKUP(A1,Sheet2!$A$2:$D$13,2,0)
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
error? or excel error message

maybe wrong text in my formula
=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"no day of week"))))

can you post the sample with error?
maybe not what you want , but i used a nested IF
Ok, I see you want to look up the month and year -
=IF(WEEKDAY(F1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(F1)&YEAR(F1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),
IF(WEEKDAY(F1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(F1)&YEAR(F1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),
IF(WEEKDAY(F1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(F1)&YEAR(F1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"Error"))))

I was working on you other post before it was closed as a duplicate - so at least i thought i would post as i had done the work

Book2
BCDEFGHIJ
14/1/205/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3#N/A#N/A#N/A#N/A#N/A#N/A#N/A
4100200300400600600700700800
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B3:H3B3=INDEX(Sheet2!$B$2:$D$13,MATCH(TEXT(B$1,"mmmm"),Sheet2!$A$2:$A$13,0),IFERROR(MATCH(TEXT(B$1,"ddd"),Sheet2!$B$1:$D$1,0),1))
B4:J4B4=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"Error"))))
Query Index .xlsx
ABCDEFGH
101-04-202002-04-202003-04-202004-04-202005-04-202006-04-202007-04-2020
2
3
4#N/A
5
Sheet1
Cell Formulas
RangeFormula
C1:H1C1=B1+1
B4B4=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"Error"))))


Query Index .xlsx
ABCD
1Mon-FriSatSun
2Apr-2020100200300
3May-2020200300400
4Jun-2020300400500
5Jul-2020400500600
6Aug-2020500600700
7Sep-2020600700800
8Oct-2020700800900
9Nov-20208009001000
10Dec-202090010001100
11Jan-2021100011001200
12Feb-2021110012001300
13Mar-2021120013001400
Sheet2


When I change the date in cell B2in sheet1 then automatically the value in cell B4 in sheet1 should change to the correct value. That is all I need.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,122
Office Version
  1. 365
Platform
  1. MacOS
yep, that should do that
When I change the date in cell B2in sheet1
you mean B1

XL2BB on a MAC does change the date to show US format , although I'm in UK

B1 is a date , as the B1+1 seems to work OK

Book2
ABCDEFGHIJK
12/1/215/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3
41100200300400600600700700800#N/A
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B4:K4B4=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"NO DATE"))))
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
yep, that should do that

you mean B1

XL2BB on a MAC does change the date to show US format , although I'm in UK

B1 is a date , as the B1+1 seems to work OK

Book2
ABCDEFGHIJK
12/1/215/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3
41100200300400600600700700800#N/A
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B4:K4B4=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0),"NO DATE"))))
Sorry. Yeah B1. I can't understand why I am still getting a #N/A error when I enter the above formula.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,122
Office Version
  1. 365
Platform
  1. MacOS
can you drop the spreadsheet onto a share ? dropbox, onedrive, maybe google sheets , if no sensitive data

your using 2019 excel on windows - so should be fine with my excel 365 on a mac
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

can you drop the spreadsheet onto a share ? dropbox, onedrive, maybe google sheets , if no sensitive data

your using 2019 excel on windows - so should be fine with my excel 365 on a mac
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
=IF(WEEKDAY(B1,2)<=5,Sheet2!B2,IF(WEEKDAY(B1,2)=6,Sheet2!C2,IF(WEEKDAY(B1,2)=7,Sheet2!D2,"")))
I tried this formula but I am getting the first 3 cells right but others I am getting 0 values.
About sharing the file through a link or something, I don't know whether it is against the rules of this forum.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,122
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

maybe you cant do the MONTH & YEAR combination in a lookup ...
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
maybe you cant do the MONTH & YEAR combination in a lookup ...
I tried vlookup in the beginning, then index match, but still the problem persists. There has to be a way. Nothing is impossible in excel.
What if, in sheet2 I seperate the Mon-Fri into 5 columns and then try. Like this
Kent.xlsx
ABCDEFGH
1MonTueWedThuFriSatSun
2Apr-2020100100100100100200300
3May-2020200200200200200300400
4Jun-2020300300300300300400500
5Jul-2020400400400400400500600
6Aug-2020500500500500500600700
7Sep-2020600600600600600700800
8Oct-2020700700700700700800900
9Nov-20208008008008008009001000
10Dec-202090090090090090010001100
11Jan-20211000100010001000100011001200
12Feb-20211100110011001100110012001300
13Mar-20211200120012001200120013001400
14
Sheet2
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,122
Office Version
  1. 365
Platform
  1. MacOS
you need the $ if copying across

Book2
BCDEFGHIJ
12/14/215/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3300100100100200100100100200
41300200300400600600700700800
5
6751362456
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B3:J3B3=IF(WEEKDAY(B1,2)<=5,Sheet2!$B2,IF(WEEKDAY(B1,2)=6,Sheet2!$C2,IF(WEEKDAY(B1,2)=7,Sheet2!$D2,"")))
B4:J4B4=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),"NO DATE")))
B6:J6B6=WEEKDAY(B1,2)


14/02/2021​
01/05/2020​
01/06/2020​
01/07/2020​
01/08/2020​
01/09/2020​
01/10/2020​
02/10/2020​
03/10/2020​
300​
100​
100​
100​
200​
100​
100​
100​
200​
200​
1300​
200​
300​
400​
600​
600​
700​
700​
800​
#N/A​
7​
5​
1​
3​
6​
2​
4​
5​
6​
6​
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,122
Office Version
  1. 365
Platform
  1. MacOS
makes it a lot more complicate , should be no need to do that for each day

<=5 is fine as is <6

playing around its still working OK for me

BUT formula had a bracket wrong
=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),
IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),
IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(MONTH(B1)&YEAR(B1),MONTH(Sheet2!$A$2:$A$13)&YEAR(Sheet2!$A$2:$A$13),0)),"NO DATE")))
 

Forum statistics

Threads
1,148,420
Messages
5,746,572
Members
424,032
Latest member
pochie2741

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