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:
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:
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).
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).