If/or index lookup based on 2 conditionals

[Dustinkli]

I'm working on a list where I need to input matching cells based on 2 columns of reference but with conditions. It's a bit hard to explain so here's an example of what I'm looking for:

Before formula:

Station	Address		Station Type	Station Ref	Address Ref
A100	Formula goes here down		Type A	C100	1.2.3.4
A101			Type A	C101	1.2.3.5
A102			Type B	C102	1.2.3.6
A103			Type C	A100	1.2.3.7
C100			Type C	A101	1.2.3.7
C101			Type C	A102	1.2.3.7
C102			Type C	A103	1.2.3.7
D200			Type A	D200	1.2.3.1
D201			Type D	D201	1.2.3.9
D202			Type D	D202	1.2.3.9
D203			Type A	D203	1.2.3.8

The empty "Address" column is where I would put the formula and what I'd like it to do is look at the "Station" column and then reference the "Station Ref" column as well as the "Address Ref" column and fill in the Address column what the matching Address Ref column says.

In addition, I would like the formula to be conditional. So for instance instead of cell B2 (Address column) saying just 1.2.3.7 I would like there to be a condition based on the "Station Type" and another condition based on whether or not the value in Address Ref is a duplicate. As the value in Address Ref is a duplicate (there are more than 1 cells with 1.2.3.7) and it is "Type C" I'd like it to say just "Type C Station" in cell B2. The same would apply for cells B3-B5. In Cell B6, as the referenced value of 1.2.3.4 is not a duplicate it will just say the corresponding Station Ref (C100) regardless of station type. The same applies for cells B7-B8.


After Formula:

Station	Address	Station Type	Station Ref	Address Ref
A100	Type C Station	Type A	C100	1.2.3.4
A101	Type C Station	Type A	C101	1.2.3.5
A102	Type C Station	Type B	C102	1.2.3.6
A103	Type C Station	Type C	A100	1.2.3.7
C100	C100	Type C	A101	1.2.3.7
C101	C101	Type C	A102	1.2.3.7
C102	C102	Type C	A103	1.2.3.7
D200	D200	Type A	D200	1.2.3.1
D201	Type D Station	Type D	D201	1.2.3.9
D202	Type D Station	Type D	D202	1.2.3.9
D203	Type D Station	Type A	D203	1.2.3.8

Here's the formula I would use if I wanted to just copy the corresponding "Station Ref" into the Address Column without any conditions: =INDEX($C$2:$C$10000,MATCH(L2,$A$2:$A$10000,0))

However, I'm not sure how to add conditions that stipulate 1. If the referenced value in column E (Station Ref) is a duplicate and 2. make it have a specific wording depending on the value in Column C (Station Type).

 

[jasonb75]

See if this work in B2 and filled down.

=IF(A2="","",INDEX($C$2:$D$12,MATCH(A2,$E$2:$E$12,0),IF(COUNTIF($E$2:$E$12,A2)=1,2,1)))

I notice that there are some address ref's in column A of your last example that are not listed in column E, these will show up as #N/A

 

[Dustinkli]

It looks to be working, Thank you Jason!