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)

 

[bebo021999]

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

Cell Formulas
Range	Formula
C1:H1	C1	=B1+1
B2:H2	B2	=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))

 

[RAJESH1960]

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

Cell Formulas
Range	Formula
C1:H1	C1	=B1+1
B2:H2	B2	=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))

Bebo021999, It's perfect. Thank you so much.

 

[RAJESH1960]

When I format the cells to date in sheet2 cells A2:A13, the formula doesn't work. Can you please help and edit the formula accordingly.

Query Index .xlsx
	A	B	C	D	E	F	G	H	I	J
1		01-04-2020	01-05-2020	01-06-2020	01-07-2020	01-08-2020	01-09-2020	01-10-2020	02-10-2020	03-10-2020
2
3		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
4
5
6
7
8
Sheet1

Cell Formulas
Range	Formula
I1:J1	I1	=H1+1
B3:H3	B3	=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))

Query Index .xlsx
	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

 

[etaf]

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
	B	C	D	E	F	G	H	I	J
1	4/1/20	5/1/20	6/1/20	7/1/20	8/1/20	9/1/20	10/1/20	10/2/20	10/3/20
2
3	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
4	100	200	300	400	600	600	700	700	800
Sheet1

Cell Formulas
Range	Formula
I1:J1	I1	=H1+1
B3:H3	B3	=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:J4	B4	=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"))))

 

[RAJESH1960]

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
	B	C	D	E	F	G	H	I	J
1	4/1/20	5/1/20	6/1/20	7/1/20	8/1/20	9/1/20	10/1/20	10/2/20	10/3/20
2
3	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
4	100	200	300	400	600	600	700	700	800
Sheet1

Cell Formulas
Range	Formula
I1:J1	I1	=H1+1
B3:H3	B3	=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:J4	B4	=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"))))

Thanks Etaf, Your earlier formula solved the problem in the sample sheet. I am trying it in my data base now.

 

[etaf]

the other formula in previous thread was wrong, as it was not looking at the month & year, it was just looking for the actual date in the table, I then re-read and saw that you wanted the results base on the table as month & year , but the day of week from the actual date

so the formula above will do that - ignore last threads info

 

[RAJESH1960]

the other formula in previous thread was wrong, as it was not looking at the month & year, it was just looking for the actual date in the table, I then re-read and saw that you wanted the results base on the table as month & year , but the day of week from the actual date

so the formula above will do that - ignore last threads info

yeah I just observed that. I have to enter the formula in 365 columns and it should match the day to get the result.

 

[RAJESH1960]

yeah I just observed that. I have to enter the formula in 365 columns and it should match the day to get the result.

I am getting a value error with this 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),"Error"))))

 

[etaf]

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?