# INDEX MATCH -> multiple results from 2 columns

#### paupaj

##### New Member
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

Last edited by a moderator:

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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

##### New Member
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.

Replies
12
Views
167
Replies
2
Views
457
Replies
6
Views
172
Replies
7
Views
90
Replies
5
Views
114