CSE formula with index/match

[Faller]

I am trying to index/match to find numbers based off of words in an array formula but when I do the match part always returns the first number it sees. So I'm only effectively adding a count multiplier.

My formula is:
{=SUM(IF(B2:J2="",0,INDEX(Food:Calories,MATCH(Calendar!B2:J2,Food,0),2)))}

I'm trying to type in what I eat in a day and have it spit out how many calories I've eaten in a day. But it always pulls from the first thing I've eaten that day and multiplies it by how many entries I put in for the day.

Thank you

 

[GlennUK]

Have a look at this example:

Excel Workbook
	A	B	C	D
1	Food	Calories		Calendar
2	Mush	100		More
3	Stuff	101		Apple
4	Whatever	102		Lemonade
5	More	103
6	Less	104		320
7	Dunno	105
8	Donuts	106
9	Apple	107
10	Banana	108
11	Melon	109
12	Lemonade	110
Sheet19

 

[T. Valko]

Have a look at this example:

Excel Workbook
	A	B	C	D
1	Food	Calories		Calendar
2	Mush	100		More
3	Stuff	101		Apple
4	Whatever	102		Lemonade
5	More	103
6	Less	104		320
7	Dunno	105
8	Donuts	106
9	Apple	107
10	Banana	108
11	Melon	109
12	Lemonade	110
Sheet19

Avoid the "whatever" at all cost!

On a side note...

I notice a lot of folks do this reverse logic thing:

...NOT(ISNA(MATCH(...

Why not just:

...ISNUMBER(MATCH(...

 

[GlennUK]

Avoid the "whatever" at all cost!

On a side note...

I notice a lot of folks do this reverse logic thing:

...NOT(ISNA(MATCH(...

Why not just:

...ISNUMBER(MATCH(...

Ha ha, you noticed my weird foodstuffs then.

As for the NOT(ISNA ... I learned to do it that way a long long time ago, and just do it automatically when I need it. I think that may true for a lot of other long time users.

 

[Faller]

I used it as a CSE but it is now returning a 0 value.

 

[Faller]

I got that part (My name was not set to the right range). It gives me a problem if I eat something twice in a day. It skips it. I assume it only looks for the first instance since the lookup value is my food.

 

[GlennUK]

Ah, I didn't know that you'd have things twice a day. Have a look at this ... the ranges have changed subtly, and there is a dummy 0 at the end of the Food/Calorie list:

Excel Workbook
	A	B	C	D
1	Food	Calories	*	Calendar
2	Mush	100	*	More
3	Stuff	101	*	Apple
4	Whatever	102	*	Lemonade
5	More	103	*	Apple
6	Less	104	*	*
7	Dunno	105	*	*
8	Donuts	106	*	*
9	Apple	107	*	*
10	Banana	108	*	*
11	Melon	109	*	*
12	Lemonade	110	*	427
13	dummy	0	*	*
Example

Defined names are:

Excel Workbook
	A	B
15	CalAndDummy	=Example!$B$2:$B$13
16	Calories	=Example!$B$2:$B$12
17	Food	=Example!$A$2:$A$12
18	FoodAndTitle	=Example!$A$1:$A$12
Example

 

[Faller]

For the life of me I can't get this to work. I've checked over my naming and can't seem to find anything wrong or different with the formula other than my match range (B2:J2). I've transposed it to see if it had to be vertical but that doesn't seem to be the case.

Any help, and the help already given, is appreciated.

Vince

 

[GlennUK]

Post your formula and describe what is in each referenced range.

 

[Faller]

=SUMPRODUCT(FREQUENCY(MATCH(B2:J2,FoodAndTitle,0),ROW(Calories))*CalAndDummy)

CalAndDummy =Values!$B$2:$B$13
Calories =Values!$B$2:$B$12
Food =Values!$A$2:$A$12
FoodAndTitle =Values!$A$1:$A$12

It lines up almost exactly by chance.

Don't know where I'm going wrong.