vlookup with if function

[RAJESH1960]

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
	A	B	C	D	E
1		Mon-Fri	Sat	Sun
2	April	100	200	300
3	May	200	300	400
4	June	300	400	500
5	July	400	500	600
6	August	500	600	700
7	September	600	700	800
8	October	700	800	900
9	November	800	900	1000
10	December	900	1000	1100
11	January	1000	1100	1200
12	February	1100	1200	1300
13	March	1200	1300	1400
14
Sheet2

Query Vlookup+If.xlsx
	A	B	C	D	E	F	G	H
1	April	01-04-2020	02-04-2020	03-04-2020	04-04-2020	05-04-2020	06-04-2020	07-04-2020
2
3		100
4
Sheet1

Cell Formulas
Range	Formula
B3	B3	=VLOOKUP(A1,Sheet2!$A$2:$D$13,2,0)

 

[RAJESH1960]

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")))

 

[RAJESH1960]

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]

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]

Try it:

Book1
	A	B	C	D	E	F	G	H
1		Wed 01/04/2020	Thu 02/04/2020	Fri 03/04/2020	Sat 04/04/2020	Sun 05/04/2020	Mon 06/04/2020	Tue 07/04/2020
2
3
4		100	100	100	200	300	100	100
Sheet1

Cell Formulas
Range	Formula
C1:H1	C1	=B1+1
B4:H4	B4	=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
	A	B	C	D
1		Mon-Fri	Sat	Sun
2	Apr-2020	100	200	300
3	May-2020	200	300	400
4	Jun-2020	300	400	500
5	Jul-2020	400	500	600
6	Aug-2020	500	600	700
7	Sep-2020	600	700	800
8	Oct-2020	700	800	900
9	Nov-2020	800	900	1000
10	Dec-2020	900	1000	1100
11	Jan-2021	1000	1100	1200
12	Feb-2021	1100	1200	1300
13	Mar-2021	1200	1300	1400
Sheet2

 

[RAJESH1960]

Try it:

Book1
	A	B	C	D	E	F	G	H
1		Wed 01/04/2020	Thu 02/04/2020	Fri 03/04/2020	Sat 04/04/2020	Sun 05/04/2020	Mon 06/04/2020	Tue 07/04/2020
2
3
4		100	100	100	200	300	100	100
Sheet1

Cell Formulas
Range	Formula
C1:H1	C1	=B1+1
B4:H4	B4	=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
	A	B	C	D
1		Mon-Fri	Sat	Sun
2	Apr-2020	100	200	300
3	May-2020	200	300	400
4	Jun-2020	300	400	500
5	Jul-2020	400	500	600
6	Aug-2020	500	600	700
7	Sep-2020	600	700	800
8	Oct-2020	700	800	900
9	Nov-2020	800	900	1000
10	Dec-2020	900	1000	1100
11	Jan-2021	1000	1100	1200
12	Feb-2021	1100	1200	1300
13	Mar-2021	1200	1300	1400
Sheet2

This also works. Thanks bebo012999