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

Titanclaymore

New Member
Joined
Nov 30, 2013
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sorry - deleted proposition as it has to be reworked
 
Last edited:
Upvote 0
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
AGHKLMNO
1Ports
2TrafalgarLos AngelesSingaporeLos AngelesSingaporeAmsterdam
3ValkyrieMobileSouthamptonTrafalgarBoston
4ValkyrieBostonAmsterdamValkyrieCapetown
5TrafalgarNew OrleansHelsinkiHelsinki
6ValkyrieLos AngelesSingaporeLos Angeles
7DoritBostonHelsinkiMobile
8Ocean StarCapetownSydneyNew Orleans
9Rose CargoLos AngelesPerthPerth
10Singapore
11Southampton
12Sydney
13
Ships
Cell Formulas
RangeFormula
L3:L4L3=FILTER(A2:A9,(G2:G9=K2)*(H2:H9=L2),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
K2:L2List=$O$2:$O$12
 
Upvote 0
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:

Excel Formula:
=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
ABCDEFGHIJKLMNOP
1NEWoriginal
2Los AngelesSingaporeTrafalgarTrafalgar
3TrafalgarLos AngelesSingaporeSingaporeValkyrieTrafalgar
4ValkyrieMobileSouthamptonPerthRose CargoRose Cargo
5ValkyrieBostonAmsterdam #N/A
6TrafalgarNew OrleansHelsinki
7ValkyrieLos AngelesSingapore
8DoritBostonHelsinki
9Ocean StarCapetownSydney
10Rose CargoLos AngelesPerth
11ValkyriePerthSingapore
Sheet1
Cell Formulas
RangeFormula
O2:O5O2=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:P5P2=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)))
 
Upvote 0
For my layout & interpretation, for older Excel versions (at back to 2010) ..

25 01 26.xlsm
AGHKL
1
2TrafalgarLos AngelesSingaporeLos AngelesSingapore
3ValkyrieMobileSouthamptonTrafalgar
4ValkyrieBostonAmsterdamValkyrie
5TrafalgarNew OrleansHelsinki 
6ValkyrieLos AngelesSingapore 
7DoritBostonHelsinki 
8Ocean StarCapetownSydney 
9Rose CargoLos AngelesPerth 
Ships (2)
Cell Formulas
RangeFormula
L3:L9L3=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))),"")
 
Upvote 0
Many thanks and apologies for the data with no spreadsheet, I will address this for the next question, if and when this arrives.

I have Office O365 and the excel version is 2501 Build 16.0.18429.20044

Many thanks for all the extra work you did to resolve this and by the looks of the sheet you have above with column O, this is the exact output I was looking to achieve, many thanks again for the solution and your extra work
 
Upvote 0
you are welcome
I would update your profile to show the excel version you have - makes a lot of difference to solutions now - as so many new functions available
so its ready for any future questions you may have
 
Upvote 0
by the looks of the sheet you have above with column O
Glad something is working for you but we do not know who you are addressing as there are two sheets above with column O.

On re-reading your original question though I think you might be referring to post #4. If so, since you have Office 365** you can get both column L and column O populated with two pretty short formulas.
If it was any use, you could even get both the relevant destinations and ships with a single formula as show in Q2 below.

** As already requested a couple of times, please update your profile so helpers always know your version.

25 01 26.xlsm
AGHKLMNOPQR
1ShipOriginDestinationOriginDestinationShipsDestinationShips
2TrafalgarLos AngelesSingaporeLos AngelesSingaporeTrafalgarSingaporeTrafalgar
3ValkyrieMobileSouthamptonSingaporeValkyrieSingaporeValkyrie
4ValkyrieBostonAmsterdamPerthRose CargoPerthRose Cargo
5TrafalgarNew OrleansHelsinki
6ValkyrieLos AngelesSingapore
7DoritBostonHelsinki
8Ocean StarCapetownSydney
9Rose CargoLos AngelesPerth
Ships
Cell Formulas
RangeFormula
L2:L4L2=FILTER(H2:H9,G2:G9=K2)
O2:O4O2=FILTER(A2:A9,G2:G9=K2)
Q2:R4Q2=FILTER(HSTACK(H2:H9,A2:A9),G2:G9=K2)
Dynamic array formulas.
 
Upvote 0
Many thanks Peter I have updated my profile and will use your suggestions and fix to enhance worksheet, thanks again
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,174
Members
453,644
Latest member
karlpravin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top