Return information in cell from 1 column looking at another two columns, where the data can be the same.

[Titanclaymore]

I have 6 key columns that have data.

In column A there is a list of fleet vessels. e.g. -

Trafalgar
Valkyrie
Valkyrie
Trafalgar
Valkyrie
Dorit
Ocean Star
Rose Cargo


In column G & H are the ports they operate between e.g. -

Los Angeles Singapore
Mobile Southampton
Boston Amsterdam
New Orleans Helsinki
Los Angeles Singapore
Boston Helsinki
Capetown Sydney
Los Angeles Perth

In column K, first cell, has a data list drop down for a port and when this is chosen, this will populate data in column L of all the ports that depart from the chosen port. e.g.

Los Angeles Singapore
Singapore
Perth

In column O, this is looking to pull data and populate the ship that operates these routes

I have used the following formula =IFERROR(INDEX($A$3:$A$49,MATCH($K$2&L2,$G$3:$G$49&$H$3:$H$49,0)),INDEX($A$3:$A$49,MATCH(L2&$K$2,$G$3:$G$49&$H$3:$H$49,0)))

This allows me to pull the ships information into the cells, however when there are two ports that are the same, as in Los Angeles Singapore, it finds the first route that matches, and returns this ship, and for the second route, this is not the correct ship. The first should be Trafalgar and the second Valkyrie

Is there a way which can identify the first route as being returned and then looks to the next matching pair and returns this match?

 

[Kaper]

Sorry - deleted proposition as it has to be reworked

 

[Peter_SSs]

Hello

One of the reasons that you have not received a suggestion until now is that it is hard to work out from your post exactly what is where on your worksheet and helpers certainly cannot just copy your sample data to test with. For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Also, we do not know what Excel version you have so we don't know what functions are available to you. I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have a recent version of Excel with these functions available, is this the sort of thing you are looking for?

25 01 26.xlsm
	A						G	H			K	L	M	N	O
1															Ports
2	Trafalgar						Los Angeles	Singapore			Los Angeles	Singapore			Amsterdam
3	Valkyrie						Mobile	Southampton				Trafalgar			Boston
4	Valkyrie						Boston	Amsterdam				Valkyrie			Capetown
5	Trafalgar						New Orleans	Helsinki							Helsinki
6	Valkyrie						Los Angeles	Singapore							Los Angeles
7	Dorit						Boston	Helsinki							Mobile
8	Ocean Star						Capetown	Sydney							New Orleans
9	Rose Cargo						Los Angeles	Perth							Perth
10															Singapore
11															Southampton
12															Sydney
13
Ships

Cell Formulas
Range	Formula
L3:L4	L3	=FILTER(A2:A9,(G2:G9=K2)*(H2:H9=L2),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
K2:L2	List	=$O$2:$O$12

 

[Kaper]

OK, The formula on the top of my original answer in post #2 was fine. But then I added IFERROR and changed adding logical conditions with OR (thyat was causing problems) in published xlBB snapshot. So that was wrong. Below is corrected solution working in old version of Excel too.

So below is my previous answer, just with edited xlBB part:

Try:

=INDEX($A$3:$A$49,SMALL(IF(($K$2&L2=$G$3:$G$49&$H$3:$H$49)+(L2&$K$2=$G$3:$G$49&$H$3:$H$49),ROW($G$3:$G$49)-2,""),COUNTIF(L$2:L2,L2)))

BTW: which version of Excel do you use now? Probably different than the one you used while registering - a dozen years ago.

And in many cases solutions using formulas introduced with modern versions are much more compact or easier to understand.



You could also add IFERROR to this formula to deal with "no data" situation like the formula from O2 being copied also to O5 (and there is no input data in L5).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1															NEW	original
2											Los Angeles	Singapore			Trafalgar	Trafalgar
3	Trafalgar						Los Angeles	Singapore				Singapore			Valkyrie	Trafalgar
4	Valkyrie						Mobile	Southampton				Perth			Rose Cargo	Rose Cargo
5	Valkyrie						Boston	Amsterdam								#N/A
6	Trafalgar						New Orleans	Helsinki
7	Valkyrie						Los Angeles	Singapore
8	Dorit						Boston	Helsinki
9	Ocean Star						Capetown	Sydney
10	Rose Cargo						Los Angeles	Perth
11	Valkyrie						Perth	Singapore
Sheet1

Cell Formulas
Range	Formula
O2:O5	O2	=IFERROR(INDEX($A$3:$A$11,SMALL(IF(($K$2&L2=$G$3:$G$11&$H$3:$H$11)+(L2&$K$2=$G$3:$G$11&$H$3:$H$11),ROW($G$3:$G$11)-2,""),COUNTIF(L$2:L2,L2))),"")
P2:P5	P2	=IFERROR(INDEX($A$3:$A$49,MATCH($K$2&L2,$G$3:$G$49&$H$3:$H$49,0)),INDEX($A$3:$A$49,MATCH(L2&$K$2,$G$3:$G$49&$H$3:$H$49,0)))

 

[Peter_SSs]

For my layout & interpretation, for older Excel versions (at back to 2010) ..

25 01 26.xlsm
	A						G	H			K	L
1
2	Trafalgar						Los Angeles	Singapore			Los Angeles	Singapore
3	Valkyrie						Mobile	Southampton				Trafalgar
4	Valkyrie						Boston	Amsterdam				Valkyrie
5	Trafalgar						New Orleans	Helsinki
6	Valkyrie						Los Angeles	Singapore
7	Dorit						Boston	Helsinki
8	Ocean Star						Capetown	Sydney
9	Rose Cargo						Los Angeles	Perth
Ships (2)

Cell Formulas
Range	Formula
L3:L9	L3	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$9)/((G$2:G$9=K$2)*(H$2:H$9=L$2)),ROWS(L$3:L3))),"")