vlookup with if function

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
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
743
Office Version
  1. 2019
Platform
  1. Windows
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")))
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
Finally, got the perfect solution. Just enter the formula in the first cell and drag it to the last cell.
'=IF(WEEKDAY(B1,2)<=5,VLOOKUP(B1,Sheet2!$A$2:$D$13,2,1),IF(WEEKDAY(B1,2)=6,VLOOKUP(B1,Sheet2!$A$2:$D$13,3,1),IF(WEEKDAY(B1,2)=7,VLOOKUP(B1,Sheet2!$A$2:$D$13,4,1),"")))
I really appreciate your time. Thanks man.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,115
Office Version
  1. 365
Platform
  1. MacOS
thats where i started but had issues because the table would need ever day of the month as you are looking up b1 complete date ie 1st
didnt think thats was what you are after

but glad its sorted now for you
 

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
287
Try it:
Cell Formulas
RangeFormula
C1:H1C1=B1+1
B4:H4B4=INDEX(Sheet2!$B$2:$D$13,MATCH(B$1,Sheet2!$A$2:$A$13),CHOOSE(WEEKDAY(B$1,2),1,1,1,1,1,2,3))

Book1
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
 
Solution

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
Try it:
Cell Formulas
RangeFormula
C1:H1C1=B1+1
B4:H4B4=INDEX(Sheet2!$B$2:$D$13,MATCH(B$1,Sheet2!$A$2:$A$13),CHOOSE(WEEKDAY(B$1,2),1,1,1,1,1,2,3))

Book1
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
This also works. Thanks bebo012999
 

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

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