I need help with an if statement that has more than 7 nested condi

  • Thread starter Thread starter Mercedes
  • Start date Start date
M

Mercedes

Hi, I am not able to get this although I thought it would be easy. The
problem is that I need more than 7 nested if statements.

I have a column with different flight numbers that correspond to different
carriers:
Flight # Carrier
1-1199 A
1300-1999 A
1200-1299 B
2000-3159 B
3160-3169 B
3200-3499 C
4900-4949 B
5500-5999 D
8635-8835 E
9105-9119 C
9491-9584 C


Then I have another column that has different flight numbers. I need an if
statement that lets me know which carrier corresponds to each flight number.
For ex in column B I have a flight number 1101. I want to get in column A
what is the carrier (in this ex it would be A).

Can anyone help me with this?

thank you so much!
 
The following should do the trick. You can get as specific as you want. I
got lazy at the end because there are gaps in the flight numbers. For what
you posted though, this will work. :)

=IF(A1<1300,"A",IF(A1<1300,"B",IF(A1<2000,"A",IF(A1<3170,"B",IF(A1<3500,"C",IF(A1<5000,"B",IF(A1<6000,"D",IF(A1<9000,"E","C"))))))))
 
Assuming the table is in H1:I12, including the headings,

=INDEX(I2:I12,MIN(IF((B2>=--LEFT(H2:H12,FIND("-",H2:H12)-1))*(B2<=--MID(H2:H12,FIND("-",H2:H12)+1,99)),ROW(H2:H12)-ROW(H2)+1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
=INDEX(I2:I12,MATCH(1,INDEX((LEFT(H2:H12,FIND("-",H2:H12)-1)+0<=B2)*(RIGHT(H2:H12,LEN(H2:H12)-FIND("-",H2:H12))+0>=B2),),))
 
Back
Top