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

[otivo]

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

 

[Aladin Akyurek]

Have a look at:

http://www.mrexcel.com/forum/excel-questions/231882-vlookup-return-all-matches.html#post1133932

 

[otivo]

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:
-----------

Monday
Make	Model	AM	PM	Total
Dodge	Caravan	4	11	15
Ford	Contour	0	5	5
Honda	Fit	1	9	10
Honda	Civic	2	3	5
Toyota	Corolla	5	11	16
Honda	Accord	1	1	2
Tuesday
Make	Model
Dodge	Charger	4	4	8
Honda	Accord	3	3	6
Honda	Civic	3	5	8
Ford	F-150	1	5	6
Honda	Pilot	5	1	6
Select One				0
Wednesday
Make	Model
Toyota	Avalon	1	2	3
Ford	Escape	5	3	8
Dodge	Durango	0	5	5
Honda	Pilot	4	4	8
Toyota	Camry	2	3	5
Dodge	Dakota	2	5	7

<tbody>
</tbody>

Sales Tab
-----------

Sales Report			Total Sold
Make:	Honda		Week1	2
Model:	Accord		Week2	3
			Week3	4

<tbody>
</tbody>

<tbody>
</tbody>

Week1 should have returned 8 sold on Mon and Tue.

Any additional assistance is most appreciated.

 

[Aladin Akyurek]

Does the Sales Tab exhibit shows the desired results? if so, how 8 bears to it?

 

[otivo]

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!

 

[Aladin Akyurek]

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

Week Day	Make	Model	AM	PM	Total
Mon	Dodge	Caravan	4	11	15
Mon	Ford	Contour	0	5	5
Mon	Honda	Fit	1	9	10
Mon	Honda	Civic	2	3	5
Mon	Toyota	Corolla	5	11	16
Mon	Honda	Accord	1	1	2
Tue	Dodge	Charger	4	4	8
Tue	Honda	Accord	3	3	6
Tue	Honda	Civic	3	5	8
Tue	Ford	F-150	1	5	6
Tue	Honda	Pilot	5	1	6

<TBODY>
</TBODY>

On the Sales sheet you can have...

Sales report			Total Sold
Make:	Honda	Week1
Make:	Honda	Week2

<TBODY>
</TBODY>

By the way, why is Total Sold for Honda in Week1 8?

 

[otivo]

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.

 

[Aladin Akyurek]

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.

The Sales sheet...

Sales report
Make:	Model:	Week:	Total Sold
Honda	Accord	Week1	8

<TBODY>
</TBODY>

D3:

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

See for Week1, the Week1 sheet, proposed earlier.

 

[otivo]

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

 

[Aladin Akyurek]

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

Here is the workbook that implements the proposals of post #6 and #8:
https://dl.dropboxusercontent.com/u/65698317/otivo%20WeekBasedConditionalSummaries.xlsx