Multiple IF for 430 argument

Question

i have a list of 430 rows which set range of phone numbers in

**Cabin   From          To** 
Cabin2  22007000    22009999
Cabin3  22010000    22059999
Cabin4  22060000    22075999
Cabin5  22310000    22369999
Cabin6  22370000    22377999
Cabin7  22380000    22399999

and i have 1500 phone number need to set which cabin it belong to i need a formula that check number in which range and print cabin name like this

**Phone        Cabin** 
22363998       Cabin5
22365005    
22381790    
22381929    
22478221    
22478222

Thanks on advance

related: http://stackoverflow.com/questions/24151047/how-to-find-location-based-on-ip-address-in-excel/24151515#24151515 — Brad, Jun 24 '14 at 18:24

score 1 · Answer 1 · answered Jun 24 '14 at 18:15

You could use INDEX(cabinArray, MATCH(phoneNumber, numberFromArray, 1))

This will match on the highest value less than the phoneNumber you lookup.

Set the Cabin column to the Named range cabinArray and the From column to the numberFromArray.

Note that this assumes the ranges cover all possible numbers and you won't be looking up a number outside the range.

Side note:

You could use some form of verification by using the more complex: if(INDEX(cabinArray, MATCH(phoneNumber, numberFromArray, 1)) = INDEX(cabinArray, MATCH(phoneNumber, numberToArray, -1)), INDEX(cabinArray, MATCH(phoneNumber, numberFromArray, 1)), "Failed to match")

This checks that the phoneNumber you are looking up is both lower than the To and greater than the From. It isn't elegant, but it does work : )

Multiple IF for 430 argument

1 Answers1