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:

Some videos you may like

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)

padapinto

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
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

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
 

padapinto

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

paupaj

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

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,640
Members
410,696
Latest member
JTrehan
Top