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

[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!

 

[StumpedAgain]

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"))))))))

 

[Bob Phillips]

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)

 

[Teethless mama]

=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),),))