vlookup with if function

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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)
 
Sort by date Sort by votes
etaf said:
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?
Click to expand...
​
etaf said:
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"))))
Click to expand...
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.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
yep, that should do that
When I change the date in cell B2in sheet1
Click to expand...
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"))))
 
Upvote 0
etaf said:
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"))))
Click to expand...
Sorry. Yeah B1. I can't understand why I am still getting a #N/A error when I enter the above formula.
 
Upvote 0
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
 
Upvote 0
etaf said:
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
Click to expand...
 
Upvote 0
=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.
 
Upvote 0
maybe you cant do the MONTH & YEAR combination in a lookup ...
 
Upvote 0
etaf said:
maybe you cant do the MONTH & YEAR combination in a lookup ...
Click to expand...
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
 
Upvote 0
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​
 
Upvote 0
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")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
797
Dave Patton
D
A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
107
anuradhagrewal
A
L
  • Solved
Importing specific cell data from one workbook to another
Replies
1
Views
370
rlv01
R
P
  • Question
Copy vlookup value to adjacent cell
Replies
1
Views
417
etaf
etaf
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
377
Legacy 352679
L

Forum statistics

Threads
1,214,642
Messages
6,120,700
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top