a_faulding
New Member
- Joined
- Jul 14, 2020
- Messages
- 8
- Office Version
- 365
Hello,
I work in travel, and I want to check whether certain routes exist in a data set.
The routes have a departure and destination values. The destination values are countries, regions or resorts:
The data set shows all possible countries, regions and resorts that can be visited from any departure location:
The logic I need to use to see whether a route is in the data set, is: "Does the Departure and Destination value exist within the same row of the data set array".
I have been able to get this to work with an array index match formula, but the limitation of this is that I have had to specify individual columns in the lookup array. For example columns A:A and B:B in the example below:
{=INDEX(A:A,MATCH("Birmingham"&"Spain",A:A&B:B,0))}
The challenge I have here, is that the table is pretty huge - with many region and resort columns. So ideally I need a means of checking the entire data set array, rather than only checking two columns at a time.
Any help appreciated!
Andrew
I work in travel, and I want to check whether certain routes exist in a data set.
The routes have a departure and destination values. The destination values are countries, regions or resorts:
Departure | Destination |
Birmingham | Spain |
Edinburgh | Costa Blanca |
Belfast | Budapest |
The data set shows all possible countries, regions and resorts that can be visited from any departure location:
Departure Location | Destination Country | Destination Region | Destination Resort |
Birmingham | Spain | Lanzarote | |
Edinburgh | Spain | Alicante | Costa Blanca |
Belfast | Hungary | Budapest |
The logic I need to use to see whether a route is in the data set, is: "Does the Departure and Destination value exist within the same row of the data set array".
I have been able to get this to work with an array index match formula, but the limitation of this is that I have had to specify individual columns in the lookup array. For example columns A:A and B:B in the example below:
{=INDEX(A:A,MATCH("Birmingham"&"Spain",A:A&B:B,0))}
The challenge I have here, is that the table is pretty huge - with many region and resort columns. So ideally I need a means of checking the entire data set array, rather than only checking two columns at a time.
Any help appreciated!
Andrew