INDEX MATCH -> multiple results from 2 columns

paupaj

New Member
Joined
Sep 16, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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

PP_excelforum3ss.png
 
Last edited by a moderator:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
appleB0.5APPLEFIRST FORMULA
capple0.750.5=IF(A1=$D$1,C1,IF(B1=$D$1,C1,""))
appleD0.80.75
Eapple0.250.8
appleF0.350.25
GBANANA0.450.35
BANANAB0.5BANANA
cBANANA0.250.5
BANANAF0.80.25
EBANANA0.40.8
BANANAF0.650.4
GBANANA280.65
 
Upvote 0
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.

excelforum3ss2.jpg
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top