How to check whether multiple criteria exist within the same row of an array?

[a_faulding]

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:

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

 

[mumps]

Have you considered using a macro? What do you want to do if the Departure and Destination value exist within the same row of the data and what do you want to do if it doesn't?

 

[a_faulding]

I have not considered a macro, no. It's been a long time since I've had a go at one, but I am certainly happy to hear any suggestions anyone may have.

Just to improve clarity here, the three routes shown in the first table would all match with rows in the data table based on my logic. But if Birmingham > Budapest was a route I wanted to check, it would not match because Birmingham and Budapest do not exist in the same row of the data set (even though they do exist in different columns).

As long as I can identify the matches, the output doesn't really matter. TRUE/FALSE or similar would be fine.

 

[Fluff]

Hi & welcome to MrExcel.
Two options, depending on whether you have the Filter function.

+Fluff New.xlsm
	A	B	C	D
1	Departure Location	Destination Country	Destination Region	Destination Resort
2	Birmingham	Spain	Lanzarote
3	Edinburgh	Spain	Alicante	Costa Blanca
4	Belfast	Hungary	Budapest
5
6
7
8
9
10	Departure	Destination	Filter	Index
11	Birmingham	Spain	TRUE	TRUE
12	Edinburgh	Costa Blanca	TRUE	TRUE
13	Belfast	Budapest	TRUE	TRUE
14	Edinburgh	Lanzarote	FALSE	FALSE
Main

Cell Formulas
Range	Formula
C11:C14	C11	=ISNUMBER(SEARCH(B11,TEXTJOIN(",",,FILTER($B$2:$D$4,$A$2:$A$4=A11))))
D11:D14	D11	=ISNUMBER(SEARCH(B11,TEXTJOIN(,,INDEX($B$2:$D$4,MATCH(A11,$A$2:$A$4,0),COLUMN(A1:C1)))))

 

[mumps]

It looks like Fluff has given you a solution.

 

[a_faulding]

Thanks for the response. Unfortunately I don't have the FILTER function, and I don't think the index match is quite there.

It works in the example above, but comes unstuck when departure locations appear on multiple occasions throughout the sheet.

I believe this is because the indexed cell is determined by the first row that is matched.

For example, if I want to check whether the Belfast > Lanzarote route exists in the table below, the suggested index match formula returns FALSE:

Departure Location	Destination Country	Destination Region	Destination Resort
Birmingham	Spain	Lanzarote
Edinburgh	Spain	Alicante	Costa Blanca
Belfast	Hungary	Budapest
Belfast	Spain	Lanzarote

 

[Fluff]

In that case how about

+Fluff New.xlsm
	A	B	C	D
1	Departure Location	Destination Country	Destination Region	Destination Resort
2	Birmingham	Spain	Lanzarote
3	Edinburgh	Spain	Alicante	Costa Blanca
4	Belfast	Hungary	Budapest
5	Belfast	Spain	Lanzarote
6
7
8
9
10	Departure	Destination
11	Birmingham	Spain	TRUE
12	Edinburgh	Costa Blanca	TRUE
13	Belfast	Lanzarote	TRUE
14	Edinburgh	Lanzarote	FALSE
Main

Cell Formulas
Range	Formula
C11:C14	C11	=ISNUMBER(SEARCH(B11,TEXTJOIN(,,INDEX($B$2:$D$5,AGGREGATE(15,6,(ROW($B$2:$B$5)-ROW($B$2)+1)/($A$2:$A$5=A11),COUNTIF($A$2:$A$5,A11)),COLUMN(A1:C1)))))

 

[a_faulding]

That's the one! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.