# Index & Match to produce multiple results from multiple column under 2 criteria

#### pulut

##### New Member
Hello Everyone!

I've been trying to get several values from several column which meets 2 criteria , concatenate them in one cell
 A B C D E F G H I J K L M 1 05/09/2014 06/09/2014 07/09/2014 2 Market Apple ripe fiji \$0.60 Grapes green Sweet \$1.00 lemon ripe sour \$0.25 3 Farm Orange ripe Naval \$0.50 lemon ripe sour \$0.25 Apple ripe fiji \$0.60 4 Friends Grapes green Sweet \$1.00 Apple ripe fiji \$0.60 Grapes green Sweet \$1.00 5 Vendor lemon ripe sour \$0.25 Orange ripe Naval \$0.50 lemon ripe sour \$0.25 6 7 Location Market Fruits Grapes Green Green 8 Date 06/09/2014 Fruits (Grapes)-green-sweet-\$1.00

<tbody>
</tbody>

on cellE7=INDEX(B2:M5,MATCH(B7,A2:A5,0),MATCH(B8,B1:M1,0))
cellF7 =INDEX(B2:M5,MATCH(B7,A2:A5,0),MATCH(B8,A1:M1,0))
cellG7 = I don't know how to get "sweet"

Questions
1.How do I do index & match for cell G7 to get the result as "sweet"
2.Why do I need to change the col array from B1 to A1 in order to get "green"
3. How do I concatenate these 4 values from several columns that share the same date?

Thank you so much for your help!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Here's a solution.

Excel 2010
ABCDEFGHIJKLM
15/9/20146/9/20147/9/2014
2MarketAppleripefiji\$0.60GrapesgreenSweet\$1.00lemonripesour\$0.25
3FarmOrangeripeNaval\$0.50lemonripesour\$0.25Appleripefiji\$0.60
4FriendsGrapesgreenSweet\$1.00Appleripefiji\$0.60GrapesgreenSweet\$1.00
5Vendorlemonripesour\$0.25OrangeripeNaval\$0.50lemonripesour\$0.25
6
7LocationMarketFruitsGrapesgreenSweet\$1.00
8Date6/9/2014Fruits(Grapes)-green-Sweet-\$1.00
Sheet3
Cell Formulas
RangeFormula
E7=INDEX(\$B\$2:\$M\$5,MATCH(\$B\$7,\$A\$2:\$A\$5,0),MATCH(\$B\$8,\$B\$1:\$M\$1,0))
E8="("&E7&")-"&F7&"-"&G7&"-"&DOLLAR(H7)
F7=INDEX(\$B\$2:\$M\$5,MATCH(\$B\$7,\$A\$2:\$A\$5,0),MATCH(\$B\$8,\$B\$1:\$M\$1,0)+COLUMNS(\$F\$7:F7))
G7=INDEX(\$B\$2:\$M\$5,MATCH(\$B\$7,\$A\$2:\$A\$5,0),MATCH(\$B\$8,\$B\$1:\$M\$1,0)+COLUMNS(\$F\$7:G7))
H7=INDEX(\$B\$2:\$M\$5,MATCH(\$B\$7,\$A\$2:\$A\$5,0),MATCH(\$B\$8,\$B\$1:\$M\$1,0)+COLUMNS(\$F\$7:H7))

Thank you Bruce. I am wondering is it possible for me to directly concatenate the combination from E7 to H7 in one cell that would yield result of E8 rather than do it at individual cells and concatenate later?

Again thank you so much for your help

Replies
1
Views
207
Replies
4
Views
1K
Replies
2
Views
423
Replies
3
Views
447
Replies
3
Views
380

1,196,479
Messages
6,015,447
Members
441,895
Latest member
Zululander

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

### Which adblocker are you using?

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

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