INDEX MATCH with multiple columns

J

jewsbur0

New Member
Joined
Oct 19, 2016
Messages
4
Hi,

I'm stuck with a Match INdex formula.
pasted_image_at_2016_10_18_04_56_pm.png


In the example above, how can I get the fruit that matches with the date to appear in column G? Is Match Index the right formula or is there something I could be using? Cheers!
 
Sort by date Sort by votes
Just realised the image hasn't posted. The formula I'm trying to get will go in B7 next to 01-Jan.

What I'm basically trying to do is get the fruit that was sold on every date. Each date is unique, there are no date duplicates. (The data I'm actually using isn't fruits and dates, but easier this way!)

[TABLE="width: 444"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Sold on[/TD]
[TD]Sold on2[/TD]
[TD]Sold on3[/TD]
[TD]Sold on4[/TD]
[TD]Sold on5[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]07-Jun[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]04-Jan[/TD]
[TD="align: right"]02-Feb[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]05-Apr[/TD]
[TD="align: right"]10-Dec[/TD]
[TD="align: right"]11-Nov[/TD]
[TD="align: right"]09-Sep[/TD]
[TD="align: right"]07-Jul[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]What[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Jan[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Apr[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-May[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Feb[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-Dec[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Jun[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Nov[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09-Sep[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Jul[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Suppose your data starting from a1 to f4

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64, align: left"]Product
[/TD]
[TD="width: 64, align: left"]Sold on[/TD]
[TD="width: 64, align: left"]Sold on2[/TD]
[TD="width: 64, align: left"]Sold on3[/TD]
[TD="width: 64, align: left"]Sold on4[/TD]
[TD="width: 64, align: left"]Sold on5[/TD]
[/TR]
[TR]
[TD="width: 64, align: left"]Apples[/TD]
[TD="width: 64"]01-Jan[/TD]
[TD="width: 64"]01-May[/TD]
[TD="width: 64"]07-Jun[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64, align: left"]Pears[/TD]
[TD="width: 64"]04-Jan[/TD]
[TD="width: 64"]02-Feb[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64, align: left"]Bananas[/TD]
[TD="width: 64"]05-Apr[/TD]
[TD="width: 64"]10-Dec[/TD]
[TD="width: 64"]11-Nov[/TD]
[TD="width: 64"]09-Sep[/TD]
[TD="width: 64"]07-Jul[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64, align: left"]Date[/TD]
[TD="width: 64, align: left"]What[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]07-Jun[/TD]
[TD="width: 64"]Apples[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]04-Jan[/TD]
[TD="width: 64"]Pears[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]05-Apr[/TD]
[TD="width: 64"]Bananas[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]01-May[/TD]
[TD="width: 64"]Apples[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]02-Feb[/TD]
[TD="width: 64"]Pears[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]10-Dec[/TD]
[TD="width: 64"]Bananas[/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]07-Jun[/TD]
[TD="width: 64"]Apples[/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]11-Nov[/TD]
[TD="width: 64"]Bananas[/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]09-Sep[/TD]
[TD="width: 64"]Bananas[/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]07-Jul[/TD]
[TD="width: 64"]Bananas[/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]





IN B7


Code: 
=index($a$2:$a$4,sumproduct(--(a13=$b$2:$f$4)*row($b$2:$f$4))-1)
 
Last edited:
Upvote 0
https://1drv.ms/x/s!AntW1ZrWMeX2gQNq01G4gmqqiJfO

ok i am very new to excel - and it does bamboozle me a lot!!!
what i am trying to do is to get the savings total to show up on the dash board just like income, expenses show up, after several hours of research i managed to get the "chart data page" to show up.
i then added the savings line (right at the bottom of "chart data" page) but i can not get it to show the right amounts on the dashboard page. I believe it is to do with the formula i have used for savings on the "chart data" page (=INDEX(Budget!C13:O13,,period)
could someone help me get the right formula so my dashboard up dates properly (Dashboard page has a scroll button at the bottom for each month - amounts should update as you scroll through the months)
let me know if this make sense or you need more info :)
thank you very much
 
Upvote 0
@ Billsmith90 - you are not allowed to hijack another person's thread. You need to start your own.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
794
henrik2h
H
E
  • Question Question
Index with match and (maybe) offset?
2
Replies
10
Views
525
Peter_SSs
Peter_SSs
M
  • Solved
Match with criteria and starting index
Replies
4
Views
213
mrblister
M
A
  • Question Question
Sumifs or sumproduct with multiple criteria horizontal and vertical
Replies
1
Views
682
DanteAmor
DanteAmor
C
  • Question Question
Transpose vertical CSV data set into a linked horizontal table with index match and/or lookup and multiple criteria
Replies
1
Views
94
Fluff
Fluff

Share this page

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