INDEX MATCH -> multiple results from 2 columns

[paupaj]

Hello,

I'm trying to get 2 multiple results from 2 different columns under 1 column and in order.

Column E = Matching Apple in column A -> returning C value
{=INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($A$1:$A$6,$E$1,0)),MATCH(ROW($A$1:$A$6),ROW($A$1:$A$6)),""),ROWS($A$1:A1)))}

Column F = Matching Apple in column B -> returning negative C value
{=-INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($B$1:$B$6,$F$1,0)),MATCH(ROW($B$1:$B$6),ROW($B$1:$B$6)),""),ROWS($B$1:B1)))}

Column G = THE GOAL to get results like this

Also asked here INDEX MATCH -> multiple results from 2 columns

 

[padapinto]

Hello,

I'm trying to get 2 multiple results from 2 different columns under 1 column and in order.

Column E = Matching Apple in column A -> returning C value
{=INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($A$1:$A$6,$E$1,0)),MATCH(ROW($A$1:$A$6),ROW($A$1:$A$6)),""),ROWS($A$1:A1)))}

Column F = Matching Apple in column B -> returning negative C value
{=-INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($B$1:$B$6,$F$1,0)),MATCH(ROW($B$1:$B$6),ROW($B$1:$B$6)),""),ROWS($B$1:B1)))}

Column G = THE GOAL to get results like this

Also asked here INDEX MATCH -> multiple results from 2 columns

View attachment 23051

if i was the one with this data, i would try nested if function without index n match and also use vlookup maybe. ask a precise question as per the table you have provided then i see what am suppost to look for

 

[Fluff]

ask a precise question as per the table you have provided then i see what am suppost to look for

It's in the OP's question

Column G = THE GOAL to get results like this

 

[padapinto]

It's in the OP's question

The names apple n banana have been entered manually but you can use a formula to find them if you are not entering them manually

apple	B	0.5	APPLE		FIRST FORMULA
c	apple	0.75	0.5		=IF(A1=$D$1,C1,IF(B1=$D$1,C1,""))
apple	D	0.8	0.75
E	apple	0.25	0.8
apple	F	0.35	0.25
G	BANANA	0.45	0.35
BANANA	B	0.5	BANANA
c	BANANA	0.25	0.5
BANANA	F	0.8	0.25
E	BANANA	0.4	0.8
BANANA	F	0.65	0.4
G	BANANA	28	0.65

 

[paupaj]

I have modified my sheet to illustrate the issue better.

I am trying to match D1 value from both A & B columns and get back the result from C column. Values matching B column have to be opposite.
I want to skip values that are not found in both A & B columns and return values only when there is a match.

D2 formula: {=(A2:A11=$D$1)*C2:C11-(B2:B11=$D$1)*C2:C11}

Column E = THE GOAL to get results like this.