Array Formula Suggestion? INDEX/MATCH

[CyrusTheVirus]

Hi everyone,

I'm looking for a different way of writing the below formula. The only way I could make it work was by combining it into an IF function...

Basically, I'm trying to make the index/match populate a value ONLY if it meets one of two criteria... the criteria being in cells D1 ("def") and E1 ("ghi")

Entered with or without(?) CSE in cell E4, then copied down to E8...

=IFERROR(IF(OR(INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0))=$D$1:$E$1),INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0)),""),"")

	A	B	C	D	E
1				def	ghi
2
3
4	corey	abc		corey	blank = formula
5	derek	def		adam	ghi = formula
6	adam	ghi		chris	blank = formula
7	josh	jkl		josh	blank = formula
8				derek	def = formula

<tbody>
</tbody>

Also curious​ exactly​ why the formula works with or without using CSE...?

 

[XOR LX]

Hi.

Perhaps (non-CSE):

=IFERROR(LOOKUP(1,0/(LOOKUP(1,0/(A$4:A$7=D4),B$4:B$7)=D$1:E$1),D$1:E$1),"")

Regards

 

[Nishant94]

Your formula without CSE:

Excel 2013 32 bit
	A	B	C	D	E
1				def	ghi
2
3
4	corey	abc		corey
5	derek	def		adam	ghi
6	adam	ghi		chris
7	josh	jkl		josh
8				derek	def
9
Sheet18

In E5 use this without CSE:

=IFERROR(IF(SUMPRODUCT(--(INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0))=$D$1:$E$1))>0,INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0)),""),"")

Your formula requires CSE because of the following red part:

[COLOR=#333333]=IFERROR(IF(OR([/COLOR][COLOR=#ff0000]INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0))=$D$1:$E$1[/COLOR][COLOR=#333333]),INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0)),""),"")[/COLOR]

 

[Aladin Akyurek]

In E4 just enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($D$1:$E$1,VLOOKUP(D4,A:B,2,0)),$D$1:$E$1),"")

 

[CyrusTheVirus]

Hi XOR LX,

That works! Thanks for the help. I've never actually used the LOOKUP function. I'll have to learn about it.

 

[CyrusTheVirus]

Hi Nishant94,

I like it. That works. Thanks for your help.

 

[CyrusTheVirus]

Hi Aladin,

That's a clever one. It works. Thanks.