Nested IF formula with multiple criteria not working

C

CaptainGravyBum

Board Regular
Joined
Dec 1, 2023
Messages
76
Office Version
  1. 365
Platform
  1. Windows
OK, I've tried my best, but I just can't find out what is going on with this formula. Chat GPT is usually a little helpful, but even that is stumped - you can't beat good old fashioned brain power so I'm hoping you can help.

The link to my file is below, I'm trying to add a formula in column R of the upload template sheet which looks for various criteria to be met before providing a result - mainly because there is a lot of error checking due to the data being provided by various people who can make errors. I can get cell R2 to return 8:00 which is correct, but then everything else is false, as though it looks at the first IF and ignores all others. At the moment it's ignoring the cells which don't return a time which is what I want, but also returning false for the cells where there should be data.

=IFERROR(
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("AM",Q2)), 'Vigo Data'!AF1 >= TIME(5, 0, 0), 'Vigo Data'!AF1 <= TIME(11, 59, 0)), 'Vigo Data'!AF1,"")&
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("PM", Q2)), 'Vigo Data'!AF1 >= TIME(12, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")&
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("TIME", Q2)), 'Vigo Data'!AF1 >= TIME(0, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")))),"")



www.dropbox.com

Upload Master Copy.xlsm

Shared with Dropbox
www.dropbox.com www.dropbox.com
 
Sort by date Sort by votes
CaptainGravyBum said:
Yeah, that was just to make sure the columns where the correct letters.
Click to expand...
The mini sheet knows which columns they are and will reflect them accurately.

Now, which conditions should return the different manually entered times?
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
dreid1011 said:
The mini sheet knows which columns they are and will reflect them accurately.

Now, which conditions should return the different manually entered times?
Click to expand...
I may be asking it to do too much, but if column Q is "AM" it will need the time from the vigo sheet AF column that needs to be between 5am and 11:59 if that's not there it should return 10:00
if Q says "PM" it needs a time between 12:00 and 23:59 in column AF, it is isn't there it needs to return 14:00
if Q says "TIME" it needs a time between 00:01 and 23:59 in column AF, it is isn't there it needs to return 11:00
If none of the conditions are met the cell should be blank.
 
Upvote 0
Ok, reworked the formula a little bit. See if this does what you want: (ignore the results in column R)
Book1
QRS
1ServiceDelivery Time
2AM0.33333333333333314:0011:008:00:00 AM
3PM10:000.58333333333333311:002:00:00 PM
4TIME 11:00
5ND  
6EC  
7ND  
8ND  
Sheet1
Cell Formulas
RangeFormula
R2:R8R2=IFERROR(IF('Vigo Data'!AF1>0,IF(AND(Q2="AM", 'Vigo Data'!AF1 >= TIME(5, 0, 0), 'Vigo Data'!AF1 <= TIME(11, 59, 0)), 'Vigo Data'!AF1,"10:00"),"")&IF('Vigo Data'!AF1>0,IF(AND(Q2="PM", 'Vigo Data'!AF1 >= TIME(12, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"14:00"),"")&IF('Vigo Data'!AF1>0,IF(AND(Q2="TIME", 'Vigo Data'!AF1 >= TIME(0, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"11:00"),""),"")
S2:S8S2=IFERROR(IF('Vigo Data'!AF1<>"",IF(AND(Q2="AM",OR('Vigo Data'!AF1 <= TIME(5, 0, 0),'Vigo Data'!AF1 >= TIME(11, 59, 0))),"10:00",IF(AND(Q2="PM",OR('Vigo Data'!AF1 <= TIME(12, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"14:00",IF(AND(Q2="TIME",OR('Vigo Data'!AF1 <= TIME(0, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"11:00",'Vigo Data'!AF1))),""),"")


Book1
ACADAEAF
1GB24A453488:00:00 AM
2GB24P453482:00:00 PM
3GB24T4534812:00:00 AM
4GB2445348
5GB4845349
6GB2445348
7GB2445348
Vigo Data
 
Upvote 1
dreid1011 said:
=IFERROR(IF('Vigo Data'!AF1<>"",IF(AND(Q2="AM",OR('Vigo Data'!AF1 <= TIME(5, 0, 0),'Vigo Data'!AF1 >= TIME(11, 59, 0))),"10:00",IF(AND(Q2="PM",OR('Vigo Data'!AF1 <= TIME(12, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"14:00",IF(AND(Q2="TIME",OR('Vigo Data'!AF1 <= TIME(0, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"11:00",'Vigo Data'!AF1))),""),"")
Click to expand...
Hi @dreid1011
I tried the formula you provided, the first two cells are returning fine because there is data in the source file, I'm just not getting results when there isn't anything in the lookup cells.

Upload Copy.xlsm
QR
1ServiceDelivery Time
2AM08:00
3PM14:00
4TIME 
5ND 
6EC 
7ND 
8ND 
Upload Template
Cell Formulas
RangeFormula
Q2:Q8Q2=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8R2=IFERROR(IF('Vigo Data'!AF1<>"",IF(AND(Q2="AM",OR('Vigo Data'!AF1 <= TIME(5, 0, 0),'Vigo Data'!AF1 >= TIME(11, 59, 0))),"10:00",IF(AND(Q2="PM",OR('Vigo Data'!AF1 <= TIME(12, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"14:00",IF(AND(Q2="TIME",OR('Vigo Data'!AF1 <= TIME(0, 1, 0), 'Vigo Data'!AF1 >= TIME(23, 59, 0))),"11:00",'Vigo Data'!AF1))),""),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S4,R:RCell Value="ENTER TIME"textNO
 
Upvote 0
Ok, I have fixed the problem above, the less than more than lookups were the wrong way round. Only trouble is that I now get FALSE when the criteria isn't met at any stage.
I tried iferror to get rid, but it doesn't work, any thoughts?
Upload Copy.xlsm
QR
1ServiceDelivery Time
2AM08:00
3PM14:00
4TIME11:00
5NDFALSE
6ECFALSE
7NDFALSE
8NDFALSE
Upload Template
Cell Formulas
RangeFormula
Q2:Q8Q2=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8R2=IFERROR(IF(Q2="AM",IF(AND('Vigo Data'!$AF1>=TIME(5,0,0),'Vigo Data'!$AF1<=TIME(11,59,0)),'Vigo Data'!$AF1,"10:00"),IF(Q2="PM",IF(AND('Vigo Data'!$AF1>=TIME(12,1,0),'Vigo Data'!$AF1<=TIME(23,59,0)),'Vigo Data'!$AF1,"14:00"),IF(Q2="TIME",IF(AND('Vigo Data'!$AF1 >= TIME(5, 0, 0),'Vigo Data'!$AF1 <= TIME(23, 59, 0)),'Vigo Data'!$AF1,"11:00")))),"")
 
Upvote 0
CaptainGravyBum said:
Ok, I have fixed the problem above, the less than more than lookups were the wrong way round. Only trouble is that I now get FALSE when the criteria isn't met at any stage.
I tried iferror to get rid, but it doesn't work, any thoughts?
Upload Copy.xlsm
QR
1ServiceDelivery Time
2AM08:00
3PM14:00
4TIME11:00
5NDFALSE
6ECFALSE
7NDFALSE
8NDFALSE
Upload Template
Cell Formulas
RangeFormula
Q2:Q8Q2=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8R2=IFERROR(IF(Q2="AM",IF(AND('Vigo Data'!$AF1>=TIME(5,0,0),'Vigo Data'!$AF1<=TIME(11,59,0)),'Vigo Data'!$AF1,"10:00"),IF(Q2="PM",IF(AND('Vigo Data'!$AF1>=TIME(12,1,0),'Vigo Data'!$AF1<=TIME(23,59,0)),'Vigo Data'!$AF1,"14:00"),IF(Q2="TIME",IF(AND('Vigo Data'!$AF1 >= TIME(5, 0, 0),'Vigo Data'!$AF1 <= TIME(23, 59, 0)),'Vigo Data'!$AF1,"11:00")))),"")
Click to expand...
Actually, I think that I've solved that as well now

Upload Copy.xlsm
QR
1ServiceDelivery Time
2AM08:00
3PM14:00
4TIME11:00
5ND 
6EC 
7ND 
8ND 
Upload Template
Cell Formulas
RangeFormula
Q2:Q8Q2=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8R2=IF(Q2="AM",IF(AND('Vigo Data'!$AF1>=TIME(5,0,0),'Vigo Data'!$AF1<=TIME(11,59,0)),'Vigo Data'!$AF1,"10:00"),IF(Q2="PM",IF(AND('Vigo Data'!$AF1>=TIME(12,1,0),'Vigo Data'!$AF1<=TIME(23,59,0)),'Vigo Data'!$AF1,"14:00"),IF(Q2="TIME",IF(AND('Vigo Data'!$AF1 >= TIME(5, 0, 0),'Vigo Data'!$AF1 <= TIME(23, 59, 0)),'Vigo Data'!$AF1,"11:00"),"")))
 
Upvote 0
Solution
CaptainGravyBum said:
Actually, I think that I've solved that as well now

Upload Copy.xlsm
QR
1ServiceDelivery Time
2AM08:00
3PM14:00
4TIME11:00
5ND 
6EC 
7ND 
8ND 
Upload Template
Cell Formulas
RangeFormula
Q2:Q8Q2=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8R2=IF(Q2="AM",IF(AND('Vigo Data'!$AF1>=TIME(5,0,0),'Vigo Data'!$AF1<=TIME(11,59,0)),'Vigo Data'!$AF1,"10:00"),IF(Q2="PM",IF(AND('Vigo Data'!$AF1>=TIME(12,1,0),'Vigo Data'!$AF1<=TIME(23,59,0)),'Vigo Data'!$AF1,"14:00"),IF(Q2="TIME",IF(AND('Vigo Data'!$AF1 >= TIME(5, 0, 0),'Vigo Data'!$AF1 <= TIME(23, 59, 0)),'Vigo Data'!$AF1,"11:00"),"")))
Click to expand...
I'm not sure how or why you were getting different results than I was unless your description of what you wanted wasn't accurate, but I turned the '>=' '<=' symbols around on purpose. Either way, glad you got it working.
 
Upvote 1
dreid1011 said:
I'm not sure how or why you were getting different results than I was unless your description of what you wanted wasn't accurate, but I turned the '>=' '<=' symbols around on purpose. Either way, glad you got it working.
Click to expand...
Yes, not sure what changed, but I got to the correct answer with your help.
Thanks for your persistence getting it working 👍
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Formula Date Format/Input Error?
Replies
7
Views
374
Alex Blakenburg
A
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
856
paulkc
P
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
400
XLfriend777
XLfriend777
Lacan
  • Solved
How to Filter data with criteria and sort it accordingly to another List rank position?
Replies
9
Views
280
Lacan
Lacan
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
868
etaf
etaf

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top