vlookup with if function

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
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)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJ
101-04-202001-05-202001-06-202001-07-202001-08-202001-09-202001-10-202002-10-202003-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
RangeFormula
I1:J1I1=H1+1
B3:H3B3=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
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,117
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
BCDEFGHIJ
14/1/205/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3#N/A#N/A#N/A#N/A#N/A#N/A#N/A
4100200300400600600700700800
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B3:H3B3=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:J4B4=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

Well-known Member
Joined
Mar 26, 2020
Messages
744
Office Version
  1. 2019
Platform
  1. Windows
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
BCDEFGHIJ
14/1/205/1/206/1/207/1/208/1/209/1/2010/1/2010/2/2010/3/20
2
3#N/A#N/A#N/A#N/A#N/A#N/A#N/A
4100200300400600600700700800
Sheet1
Cell Formulas
RangeFormula
I1:J1I1=H1+1
B3:H3B3=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:J4B4=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

Well-known Member
Joined
Oct 24, 2012
Messages
5,117
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

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

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

Well-known Member
Joined
Oct 24, 2012
Messages
5,117
Office Version
  1. 365
Platform
  1. MacOS
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?
 

Forum statistics

Threads
1,148,394
Messages
5,746,443
Members
424,020
Latest member
LongDoo

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