Excel 2010, INDEX MATCH only returns first value instead of all matching values in selected column

O

otivo

New Member
Joined
Jun 18, 2010
Messages
6
Excel 2010:
I’m working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.

The Make-Models tab has the lists used to create the drop-down selections.

The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.

Problem:
When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.

2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.

Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.

IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))

Kindly review and assist with information to address the issue described.

Note: I am unable/unauthorized to post attachments. If you need me to, please let me know how I can submit the file for you to review.

Thanks!!

Mzee
 
Sort by date Sort by votes
Thanks for your response. I looked at the post you referred me to and attempted several formulas but still couldn't get the results I'm looking for. Since I can not attach a file, I'm pasting the dataset below for ease of reference:

Week1 Tab:
-----------[TABLE="width: 349"]
<tbody>[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]AM[/TD]
[TD]PM[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Dodge[/TD]
[TD]Caravan[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]Contour[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Fit[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Civic[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Corolla[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Accord[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Make[/TD]
[TD]Model[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dodge[/TD]
[TD]Charger[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Accord[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Civic[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]F-150[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Pilot[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Select One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Make[/TD]
[TD]Model[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Avalon[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]Escape[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Dodge[/TD]
[TD]Durango[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Pilot[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Camry[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Dodge[/TD]
[TD]Dakota[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7


[/TD]
[/TR]
</tbody>[/TABLE]
Sales Tab
-----------
[TABLE="width: 269"]
<tbody>[TR]
[TD="colspan: 2"]Sales Report[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Sold[/TD]
[/TR]
[TR]
[TD]Make:[/TD]
[TD]Honda[/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Model:[/TD]
[TD]Accord[/TD]
[TD][/TD]
[TD]Week2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Week3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 269"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Week1 should have returned 8 sold on Mon and Tue.

Any additional assistance is most appreciated.
 
Upvote 0
No, that's where the problem lies... I expected the Sales tab to return 8 Honda Accords for Week 1 due to the following activity:

Monday
Make Model AM PM Total
Honda Accord 1 1 2


Tuesday
Honda Accord 3 3 6
------
8


Therefore, the Sales tab should show 8, not 2 for Week1. Current formular {IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))} is only returning the first match of 2, not adding the 6 in the same week)

I can send you the worksheet if you like for a closer review.

Thanks!
 
Upvote 0
May I suggest that you consider the following layout for the weekly sheets... Below Week1 with Mon and Tue...


[TABLE="width: 452"]
<TBODY>[TR]
[TD="class: xl66, width: 117, bgcolor: white"]Week Day
[/TD]
[TD="class: xl66, width: 108, bgcolor: white"]Make
[/TD]
[TD="class: xl66, width: 116, bgcolor: white"]Model
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]AM
[/TD]
[TD="class: xl66, width: 73, bgcolor: white"]PM
[/TD]
[TD="class: xl66, width: 105, bgcolor: white"]Total
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Dodge
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Caravan
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]4
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]11
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]15
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Ford
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Contour
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]0
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]5
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]5
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Fit
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]9
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]10
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Civic
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]5
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Toyota
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Corolla
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]5
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]11
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]16
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Mon
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Accord
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]2
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Tue
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Dodge
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Charger
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]4
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]4
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]8
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Tue
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Accord
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]6
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Tue
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Civic
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]5
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]8
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Tue
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Ford
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]F-150
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]5
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]6
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 117, bgcolor: white"]Tue
[/TD]
[TD="class: xl65, width: 108, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Pilot
[/TD]
[TD="class: xl65, width: 82, bgcolor: white"]5
[/TD]
[TD="class: xl65, width: 73, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]6
[/TD]
[/TR]
</TBODY>[/TABLE]

On the Sales sheet you can have...

[TABLE="width: 278"]
<TBODY>[TR]
[TD="class: xl65, width: 126, bgcolor: white"]Sales report
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 103, bgcolor: white"]Total Sold
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 126, bgcolor: white"]Make:
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Week1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 126, bgcolor: white"]Make:
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]Honda
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Week2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


By the way, why is Total Sold for Honda in Week1 8?
 
Upvote 0
I will try the suggested format and see if does the trick - though I'm not sure my formula will now be able to include values beyond the first match found - that's the issue I'm having. I'll give it a shot nevertheless and report back.

The Total Sold in Week1 for Honda ACCORD is 8 because 2 were sold on Mon (1 in AM, 1 in PM), 6 (3 in AM, 3 in PM) on Tue, 0 on Wed... Thur and Fri not shown. Note that total is for the Accord model only, not all Hondas, and only on Week1.
 
Upvote 0
otivo said:
I will try the suggested format and see if does the trick - though I'm not sure my formula will now be able to include values beyond the first match found - that's the issue I'm having. I'll give it a shot nevertheless and report back.

The Total Sold in Week1 for Honda ACCORD is 8 because 2 were sold on Mon (1 in AM, 1 in PM), 6 (3 in AM, 3 in PM) on Tue, 0 on Wed... Thur and Fri not shown. Note that total is for the Accord model only, not all Hondas, and only on Week1.
Click to expand...

The Sales sheet...


[TABLE="width: 294"]
<TBODY>[TR]
[TD="class: xl63, width: 126, bgcolor: white"]Sales report
[/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 109, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 126, bgcolor: white"]Make:
[/TD]
[TD="class: xl63, width: 78, bgcolor: white"]Model:
[/TD]
[TD="class: xl63, width: 78, bgcolor: white"]Week:
[/TD]
[TD="class: xl63, width: 109, bgcolor: white"]Total Sold
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 126, bgcolor: white"]Honda
[/TD]
[TD="class: xl63, width: 78, bgcolor: white"]Accord
[/TD]
[TD="class: xl63, width: 78, bgcolor: white"]Week1
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
</TBODY>[/TABLE]

D3:

=SUMIFS(INDIRECT("'"&C3&"'!F:F"),INDIRECT("'"&C3&"'!B:B"),A3,INDIRECT("'"&C3&"'!C:C"),B3)

See for Week1, the Week1 sheet, proposed earlier.
 
Upvote 0
Hello,

I attempted changing the format and using the suggested formula but I couldn't get it to work I may have been doing something wrong as my references were off. Anyway, I've uploaded the original file using the link below (not password protected) so you should be able to see what I had and if you can still assist.

http://tbf.me/a/B0wUDR

Thanks again for your help!

Mzee
 
Upvote 0
otivo said:
Hello,

I attempted changing the format and using the suggested formula but I couldn't get it to work I may have been doing something wrong as my references were off. Anyway, I've uploaded the original file using the link below (not password protected) so you should be able to see what I had and if you can still assist.

http://tbf.me/a/B0wUDR

Thanks again for your help!

Mzee
Click to expand...

Here is the workbook that implements the proposals of post #6 and #8:
https://dl.dropboxusercontent.com/u/65698317/otivo%20WeekBasedConditionalSummaries.xlsx
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Selecting MAX values of 3D array via INDEX/MATCH
Replies
3
Views
318
travellerva
T
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
674
Ol Reliable
O
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
249
Pbobas
P
O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
3K
nemmi69
nemmi69
F
  • Question Question
Index min value from one column based on multiple matches in another column
Replies
2
Views
490
Anonymous1378
A

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