Index Match if one additional value is true

[jeepnfl]

Data in column A is the unit code. Column B is unit system. Column C is unit system I.D. There are 3 unit systems for each unit code and one unit system I.D. for each unit system.

I have a list of all of the unit codes and I need to match the unit code with one specific unit system and the result be the unit system I.D.

Sheet 1:
UNIT CODE UNIT SYSTEM SYSTEM I.D.
unit1324 PBUSE W42511
unit1324 ULLS4 W95478
unit1324 SAMSe W85698

Sheet 2:
I have all unit codes listed only once in column A. Column B is PBUSE, column C is ULLS4 and column D is SAMSe. In cell B2 I want to say: look on sheet 1 and if unit code matches A2 AND unit system = "PBUSE" give me the system I.D.

I'll then use the same formula to grab the system I.D.'s for the ULLS4 and SAMSe. I just can't seem to make this work. your assistance is greatly appreciated.

Randy

 

[Aladin Akyurek]

Data in column A is the unit code. Column B is unit system. Column C is unit system I.D. There are 3 unit systems for each unit code and one unit system I.D. for each unit system.

I have a list of all of the unit codes and I need to match the unit code with one specific unit system and the result be the unit system I.D.

Sheet 1:
UNIT CODE UNIT SYSTEM SYSTEM I.D.
unit1324 PBUSE W42511
unit1324 ULLS4 W95478
unit1324 SAMSe W85698

Sheet 2:
I have all unit codes listed only once in column A. Column B is PBUSE, column C is ULLS4 and column D is SAMSe. In cell B2 I want to say: look on sheet 1 and if unit code matches A2 AND unit system = "PBUSE" give me the system I.D.

I'll then use the same formula to grab the system I.D.'s for the ULLS4 and SAMSe. I just can't seem to make this work. your assistance is greatly appreciated.

Randy

Sheet2, B2

Control+shift+enter, not just enter, copy across, and down:

=INDEX(Sheet1!$C$2:$C$4,MATCH(1,IF(Sheet1!$A$2:$A$4=A2,IF(Sheet1!$B$2:$B$4=B$1,1)),0))

 

[T. Valko]

Data in column A is the unit code. Column B is unit system. Column C is unit system I.D. There are 3 unit systems for each unit code and one unit system I.D. for each unit system.

I have a list of all of the unit codes and I need to match the unit code with one specific unit system and the result be the unit system I.D.

Sheet 1:
UNIT CODE UNIT SYSTEM SYSTEM I.D.
unit1324 PBUSE W42511
unit1324 ULLS4 W95478
unit1324 SAMSe W85698

Sheet 2:
I have all unit codes listed only once in column A. Column B is PBUSE, column C is ULLS4 and column D is SAMSe. In cell B2 I want to say: look on sheet 1 and if unit code matches A2 AND unit system = "PBUSE" give me the system I.D.

I'll then use the same formula to grab the system I.D.'s for the ULLS4 and SAMSe. I just can't seem to make this work. your assistance is greatly appreciated.

Randy

One way...

Book1
	A	B	C
1	UNIT CODE	UNIT SYSTEM	SYSTEM I.D.
2	unit1324	PBUSE	W42511
3	unit1324	ULLS4	W95478
4	unit1324	SAMSe	W85698
Sheet1

Book1
	A	B	C	D
1	_	PBUSE	ULLS4	SAMSe
2	unit1324	W42511	W95478	W85698
Sheet2

Enter this array formula** in B2:

=INDEX(Sheet1!$C$2:$C$4,MATCH($A2,IF(Sheet1!$B$2:$B$4=B$1,Sheet1!$A$2:$A$4),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to D2 then down as needed.

 

[jeepnfl]

Thank you to you both. I was working with the same formula that T. Valko provided but had my references backwards. Now it's working.

For my own knowledge bank can one of you briefly explain why Ctrl+Shift+enter is different than just Enter?

Randy

 

[T. Valko]

For my own knowledge bank can one of you briefly explain why Ctrl+Shift+enter is different than just Enter?

Randy

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Aladin Akyurek]

Thank you to you both. I was working with the same formula that T. Valko provided but had my references backwards. Now it's working.

For my own knowledge bank can one of you briefly explain why Ctrl+Shift+enter is different than just Enter?

Randy

You are welcome. Thanks for providing feedback. Control+shift+enter signals Excel to treat the formula in question as one that operates on array objects. For example, A2:A3 housing the text items like JAD and KAD would be read as {"JAD";"KAD"} and A2:A3="KAD" as {FALSE;TRUE}...

 

[T. Valko]

I was working with the same formula that T. Valko provided but had my references backwards. Now it's working.
Randy

You're welcome. Thanks for the feedback!