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

jasonb75

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
It looks to be working, Thank you Jason!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,971
Messages
5,621,891
Members
415,864
Latest member
cybid

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