Two conditions for Index Match function

[westend9876]

Here is my formula in C4
=IF(H4="",INDEX(El_Camino_Data!AA:AA,MATCH(B4,El_Camino_Data!A:A,0)),H4)

However, I need to match B4 in El_Camino_Data!A:A only if El_Camino_Data!B:B <> "Points"

 

[Awesome Wally]

TRY:=IF(H4="",INDEX(El_Camino_Data!AA:AA,MATCH(B4&TRUE,El_Camino_Data!A:A&INDEX(El_Camino_Data!B:B<>"Points",0),0),H4))

 

[westend9876]

TRY:=IF(H4="",INDEX(El_Camino_Data!AA:AA,MATCH(B4&TRUE,El_Camino_Data!A:A&INDEX(El_Camino_Data!B:B<>"Points",0),0),H4))

Hi. This one does not work. The result returns #VALUE!

 

[Awesome Wally]

use Ctrl+Shift+Enter instead of single Enter

I added a quick dummy file for you to review. Note the criteria and arrangements of data are not in the same place as your data but this is a quick sample. Feel free to attach a sample of your own for better results.

 

[westend9876]

use Ctrl+Shift+Enter instead of single Enter

I added a quick dummy file for you to review. Note the criteria and arrangements of data are not in the same place as your data but this is a quick sample. Feel free to attach a sample of your own for better results.

use Ctrl+Shift+Enter instead of single Enter

I added a quick dummy file for you to review. Note the criteria and arrangements of data are not in the same place as your data but this is a quick sample. Feel free to attach a sample of your own for better results.

Thanks. Is there any way to obtain the same results without an array?

 

[Nebu]

Hi:

Here are Non-Array formulas
Single answer
=IF(E2="",INDEX($A$1:$A$8,MATCH(1,MMULT(($D$2=$A$1:$A$8)*(TRUE=INDEX($B$1:$B$8<>$D$3,0)),1),0)))
Multiple Answer
=IFERROR(INDEX($A$1:$A$8,AGGREGATE(15,6,(ROW($B$1:$B$8)-MIN(ROW($B$1:$B$8)))/(($B$1:$B$8<>$D$3)*($A$1:$A$8=$D$2)),ROW(A1))),"")

Thanks

 

[Awesome Wally]

Here is the sample file with both the array formula I purposed and the awesome non-array formula Nebu purposed. I modified both formulas to reference a fictional Price column because I think that was intended in the OP. If not you can modify back simply by changing the column reference (i.e. 2 back to 1).

 

[bosco_yip]

Here is the sample file with both the array formula I purposed and the awesome non-array formula Nebu purposed. I modified both formulas to reference a fictional Price column because I think that was intended in the OP. If not you can modify back simply by changing the column reference (i.e. 2 back to 1).

Hi,

1] In Cell J3, "Single Answer" array formula can be converted to non-array formula :

{=INDEX($A$2:$C$9,MATCH($F$2&TRUE,$A$2:$A$9&INDEX($C$2:$C$9<>$F$3,0),0),2)}

By shifting INDEX position into this place >>

=INDEX($A$2:$C$9,MATCH($F$2&TRUE,INDEX($A$2:$A$9&($C$2:$C$9<>$F$3),0),0),2)

2] In Cell K3, "Multiple Answer" array formula :

{=IFERROR(INDEX($A$2:$C$9,SMALL(IF(($C$2:$C$9<>$F$3)*(A2:A9=$F$2),ROW($1:$8),""),ROWS($1:1)+1),2),"")}

Missing absolute reference $ symbol in front of A2:A9, and remove "+1" >>

{=IFERROR(INDEX($A$2:$C$9,SMALL(IF(($C$2:$C$9<>$F$3)*($A$2:$A$9=$F$2),ROW($1:$8),""),ROWS($1:1)),2),"")}

Regards
Bosco