Excel If Statement with Wildcards and Value Search

0 votes

I want to categorise a lot of data that includes flight arrival and departure times. The data can include statements like "On Time," "34 Minutes Early," "7 Minutes Late," etc. I only want to categorise whether the flight was on time, early, or late. To accomplish this, the formula shown below has been effective:

=IF(ISNUMBER(SEARCH("*Late*",E3)),"Late",IF(ISNUMBER(SEARCH("*Early*",E3)),"Early",IF(ISNUMBER(SEARCH("*On Time*",E3)),"On Time","")))

Yet since my analysis serves a useful function, I'd want to define "on time" as everything that arrives or departs within 10 minutes of its scheduled time. The following formulas are some of the versions I've attempted.

=IF(COUNTIF(E19,"*<10*"),"On Time","") =IF(COUNTIF(E19,"<"&"*10*"),"On Time","") 

Which would then be put into my nested IF Statement above, but nothing seems to work.

Apr 2, 2023 in Others by narikkadan
• 63,680 points
502 views

1 answer to this question.

0 votes

Try this:

IFERROR(IF(IFERROR(LEFT(B2,2)*1,"")<=10,"",IFERROR(LEFT(B2,2)*1,""))*IF(IFERROR(SEARCH("*lat*",B2,1),0)=1,-1,1),"")

See: enter image description here

answered Apr 2, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,680 points
916 views
0 votes
1 answer
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,680 points
983 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,680 points
702 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,680 points
3,458 views
0 votes
1 answer

Excel If field with rounding

Here's your formula. Assumes your cell containing ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,680 points
800 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,680 points
979 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,680 points
1,535 views
0 votes
1 answer

Check multiple cells in excel, move to next cell if null and if value found stop and return that value

Use this formula: =INDEX(FILTER(A2:D2,A2:D2<>""),1,1) INDEX returns the first value ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,020 points
522 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,020 points
1,050 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP