# vlookup with if function

#### RAJESH1960

##### Well-known Member
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
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
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
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
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

#### RAJESH1960

##### Well-known Member
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

Replies
2
Views
77
Replies
2
Views
113
Replies
2
Views
228
Replies
3
Views
252
Replies
3
Views
715

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.

### Which adblocker are you using?

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

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