Hello Everyone!
I've been trying to get several values from several column which meets 2 criteria , concatenate them in one cell
[TABLE="width: 687"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 84, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 64, bgcolor: transparent"]J[/TD]
[TD="width: 64, bgcolor: transparent"]K[/TD]
[TD="width: 64, bgcolor: transparent"]L[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 276, bgcolor: yellow, colspan: 4"]05/09/2014[/TD]
[TD="width: 256, bgcolor: yellow, colspan: 4"]06/09/2014[/TD]
[TD="width: 256, bgcolor: yellow, colspan: 4"]07/09/2014[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 64, bgcolor: #92D050"]Market[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Apple[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]fiji[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.60[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Grapes[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]green[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Sweet[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$1.00[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]lemon[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]sour[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.25[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: #92D050"]Farm[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Orange[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]Naval[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.50[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]lemon[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]sour[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.25[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Apple[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]fiji[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.60[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="width: 64, bgcolor: #92D050"]Friends[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Grapes[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]green[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]Sweet[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$1.00[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Apple[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]fiji[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.60[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Grapes[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]green[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Sweet[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$1.00[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="width: 64, bgcolor: #92D050"]Vendor[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]lemon[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]sour[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.25[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Orange[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Naval[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.50[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]lemon[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]sour[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.25[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="width: 64, bgcolor: transparent"]Location[/TD]
[TD="width: 84, bgcolor: transparent"]Market[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Fruits[/TD]
[TD="width: 64, bgcolor: transparent"]Grapes[/TD]
[TD="width: 64, bgcolor: transparent"]Green[/TD]
[TD="width: 64, bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="width: 64, bgcolor: transparent"]Date[/TD]
[TD="width: 84, bgcolor: transparent"]06/09/2014[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Fruits[/TD]
[TD="width: 192, bgcolor: yellow, colspan: 3"](Grapes)-green-sweet-$1.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
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!
I've been trying to get several values from several column which meets 2 criteria , concatenate them in one cell
[TABLE="width: 687"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 84, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 64, bgcolor: transparent"]J[/TD]
[TD="width: 64, bgcolor: transparent"]K[/TD]
[TD="width: 64, bgcolor: transparent"]L[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 276, bgcolor: yellow, colspan: 4"]05/09/2014[/TD]
[TD="width: 256, bgcolor: yellow, colspan: 4"]06/09/2014[/TD]
[TD="width: 256, bgcolor: yellow, colspan: 4"]07/09/2014[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 64, bgcolor: #92D050"]Market[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Apple[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]fiji[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.60[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Grapes[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]green[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Sweet[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$1.00[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]lemon[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]sour[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.25[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: #92D050"]Farm[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Orange[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]Naval[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.50[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]lemon[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]sour[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.25[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Apple[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]fiji[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.60[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="width: 64, bgcolor: #92D050"]Friends[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]Grapes[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]green[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]Sweet[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$1.00[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Apple[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]fiji[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.60[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Grapes[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]green[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]Sweet[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$1.00[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="width: 64, bgcolor: #92D050"]Vendor[/TD]
[TD="width: 84, bgcolor: #DAEEF3"]lemon[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]ripe[/TD]
[TD="width: 64, bgcolor: #DAEEF3"]sour[/TD]
[TD="width: 64, bgcolor: #DAEEF3, align: right"]$0.25[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Orange[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]ripe[/TD]
[TD="width: 64, bgcolor: #FDE9D9"]Naval[/TD]
[TD="width: 64, bgcolor: #FDE9D9, align: right"]$0.50[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]lemon[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]ripe[/TD]
[TD="width: 64, bgcolor: #E6B8B7"]sour[/TD]
[TD="width: 64, bgcolor: #E6B8B7, align: right"]$0.25[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="width: 64, bgcolor: transparent"]Location[/TD]
[TD="width: 84, bgcolor: transparent"]Market[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Fruits[/TD]
[TD="width: 64, bgcolor: transparent"]Grapes[/TD]
[TD="width: 64, bgcolor: transparent"]Green[/TD]
[TD="width: 64, bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="width: 64, bgcolor: transparent"]Date[/TD]
[TD="width: 84, bgcolor: transparent"]06/09/2014[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Fruits[/TD]
[TD="width: 192, bgcolor: yellow, colspan: 3"](Grapes)-green-sweet-$1.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
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!