IF statement for lookup in table

[someonehelppelase]

Hi,

I am creating a travel spreadsheet for work.

I have data validation in place for the origin so that it must be one of the 628 airport codes we use internally.

What I am trying to do is set the destination so that it can only be one of the destinations available from that origin.

For example, if an individual plans to leave Albuquerque, NM (ABQ) and go to Providence, RI (PVD), that trip is not available with our internal system. Instead, the traveler would have to go to Boston, MA (BOS).

I currently have a separate tab with all our 12,000 possible combinations sorted by origin, as below. Would I have to sort by origin, then by destination alphabetically? Would it be a formula or data validation I need to use to achieve this? I also need to do the same thing but with the full city names spelled out, but I assume the same formula would work for both.

ABQ ICN
ABQ LHR
ABQ AUH
ABQ MUC
ABQ CBR
ABQ MXP
ABQ ASP
ABQ TUS
ABQ PHX
ABQ LAX
ABQ BOS
ABQ DCA

Thanks for your help!

[eibi]

This can be tough to describe, I've attached a spreadsheet to illustrate.

You need to set-up a Named Reference (Dest_List, for example) using an Offset formula something like this:

=OFFSET($A$1,MATCH($D$2,$A$2:$A$24,0),1,COUNTIF($A$2:$A$24,$D$2))

Then, in your Data Validation, you refer to that Named Reference

=Dest_List

See attached. If you have specific questions, we can work through them one at a time...

[JohnTopley]

Re eibi reply: set Departure as a Named list to be used in Data Validation.