If/or index lookup based on 2 conditionals

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
62
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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:
StationAddressStation TypeStation RefAddress Ref
A100Formula goes here downType AC1001.2.3.4
A101Type AC1011.2.3.5
A102Type BC1021.2.3.6
A103Type CA1001.2.3.7
C100Type CA1011.2.3.7
C101Type CA1021.2.3.7
C102Type CA1031.2.3.7
D200Type AD2001.2.3.1
D201Type DD2011.2.3.9
D202Type DD2021.2.3.9
D203Type AD2031.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:
StationAddressStation TypeStation RefAddress Ref
A100Type C StationType AC1001.2.3.4
A101Type C StationType AC1011.2.3.5
A102Type C StationType BC1021.2.3.6
A103Type C StationType CA1001.2.3.7
C100C100Type CA1011.2.3.7
C101C101Type CA1021.2.3.7
C102C102Type CA1031.2.3.7
D200D200Type AD2001.2.3.1
D201Type D StationType DD2011.2.3.9
D202Type D StationType DD2021.2.3.9
D203Type D StationType AD2031.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).
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Your example and explanation appear to contradict each other, is this what you need?
The List.xlsx
ABCDE
1StationAddressStation TypeStation RefAddress Ref
2A100Type C StationType AC1001.2.3.4
3A101Type C StationType AC1011.2.3.5
4A102Type C StationType BC1021.2.3.6
5A103Type C StationType CA1001.2.3.7
6C100Type A StationType CA1011.2.3.7
7C101Type A StationType CA1021.2.3.7
8C102C102Type CA1031.2.3.7
9D200Type A StationType AD2001.2.3.1
10D201Type D StationType DD2011.2.3.9
11D202Type D StationType DD2021.2.3.9
12D203Type A StationType AD2031.2.3.8
Sheet5
Cell Formulas
RangeFormula
B2:B12B2=IF(COUNTIF($C$2:$C$12,INDEX($C$2:$C$12,MATCH(A2,$D$2:$D$12,0)))=1,A2,INDEX($C$2:$C$12,MATCH(A2,$D$2:$D$12,0))&" Station")
 
Upvote 0
Does this Work ?
D203 - 1.2.3.8 only appears once and so should that be D203 ?

I think this is working
=IF(COUNTIF($E$2:$E$12,INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0)))>1,(INDEX($C$2:$C$12,MATCH((INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0))),$E$2:$E$12,0)))&" Station",A2)


Book4
ABCDEFG
1StationAddressStation TypeStation RefAddress Ref
2A100Type C StationType AC1001.2.3.4Type C Station
3A101Type C StationType AC1011.2.3.5Type C Station
4A102Type C StationType BC1021.2.3.6Type C Station
5A103Type C StationType CA1001.2.3.7Type C Station
6C100C100Type CA1011.2.3.7C100
7C101C101Type CA1021.2.3.7C101
8C102C102Type CA1031.2.3.7C102
9D200D200Type AD2001.2.3.1D200
10D201Type D StationType DD2011.2.3.9Type D Station
11D202Type D StationType DD2021.2.3.9Type D Station
12D203D203Type AD2031.2.3.8Type D Station
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=IF(COUNTIF($E$2:$E$12,INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0)))>1,(INDEX($C$2:$C$12,MATCH((INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0))),$E$2:$E$12,0)))&" Station",A2)
 
Upvote 0
Yes, D203 should say D203 as it only appears once.

When I paste it into my excel it says "Type C Station" for every cell in column B. Not sure if I'm doing something wrong.
 
Upvote 0
Your example and explanation appear to contradict each other, is this what you need?

I apologize, that's my fault. In your formula I don't see where it references column E which it would need to do in order to determine if the "Address Ref" is a duplicate or not.
 
Upvote 0
Does this Work ?
D203 - 1.2.3.8 only appears once and so should that be D203 ?

I think this is working
=IF(COUNTIF($E$2:$E$12,INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0)))>1,(INDEX($C$2:$C$12,MATCH((INDEX($E$2:$E$12,MATCH(A2,$D$2:$D$12,0))),$E$2:$E$12,0)))&" Station",A2)

I got it to work. I copied/pasted it wrong. I'll test it out and let you know.

Thank you!
 
Upvote 0
I had misread the column E criteria, looks like Wayne has figured it out though :)
 
Upvote 0
Wayne,

It looks like I made a mistake in my initial request as I mixed the columns up. My Column A is not a list of the stations but rather the addresses.

Here's what it looks like:

StationAddressStation TypeStation RefAddress Ref
1.6.4.8Type AC1001.2.3.4
1.2.3.5Type AC1011.2.3.5
1.2.3.4Type BC1021.2.3.6
1.2.3.8Type CA1001.2.3.7
1.2.3.6Type CA1011.2.3.7
1.3.9.7Type CA1021.2.3.7
1.2.3.7Type CA1031.2.3.7
1.2.3.1Type AD2001.2.3.1
1.2.3.9Type DD2011.2.3.9
1.5.7.8Type DD2021.2.3.9
1.9.7.3Type AD2031.2.3.8


So in column A I have the list of addresses and in Column C I have the type, in column D I have the station and in column E I have the address for that station.

Some of the addresses in column A don't correlate with any address in Column E so I'd like those to be blank. When an address in A does match one in E I'd like it to follow these rules:

1. If the address referenced in A is in E but only once then it will copy the corresponding "Station Ref" in Column D.
2. If the address referenced in Column A occurs more than once in column E then it should copy the corresponding cell from Column C (Station Type).



Sorry for the confusion, but I think this will solve my problems.
 
Upvote 0
I've been trying to make it work using the previous formula but I can't figure it out unfortunately.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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