Index Match Iferror function

[RAJESH1960]

This post was solved earlier but when I format the cells to date in sheet2 cells A2:A13, the formula doesn't work.
I am trying to get the values from sheet2 to sheet1 using 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.
Can someone 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]

you could use
=IF(WEEKDAY(B1,2)<6,INDEX(Sheet2!$B$2:$B$13,MATCH(Sheet1!B1,Sheet2!$A$2:$A$13,0)),
IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(Sheet1!B1,Sheet2!$A$2:$A$13,0)),
IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(Sheet1!B1,Sheet2!$A$2:$A$13,0)))))

BUT you will get N/A if no date exists in table

Book2
	A	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	#N/A	#N/A
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(Sheet1!B1,Sheet2!$A$2:$A$13,0)), IF(WEEKDAY(B1,2)=6,INDEX(Sheet2!$C$2:$C$13,MATCH(Sheet1!B1,Sheet2!$A$2:$A$13,0)), IF(WEEKDAY(B1,2)=7,INDEX(Sheet2!$D$2:$D$13,MATCH(Sheet1!B1,Sheet2!$A$2:$A$13,0)))))

 

[Fluff]

Duplicate to: vlookup with if function

Do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.