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

[pulut]

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!

 

[skywriter]

Here's a solution.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		5/9/2014				6/9/2014				7/9/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	Sweet	$1.00
8	Date	6/9/2014		Fruits	(Grapes)-green-Sweet-$1.00
Sheet3

Cell Formulas
Range	Formula
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))

 

[pulut]

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