If/or index lookup based on 2 conditionals

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
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).
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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")
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,580
Office Version
  1. 365
Platform
  1. MacOS
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)
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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.
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows

ADVERTISEMENT

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.
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,580
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

you are welcome
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
I had misread the column E criteria, looks like Wayne has figured it out though :)
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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.
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
I've been trying to make it work using the previous formula but I can't figure it out unfortunately.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,391
Messages
5,624,441
Members
416,028
Latest member
aej

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
Top