SumIF, Index & Match

[chris_haynes]

Hi

My first post andi have to say this place has helped me a lot in the past so thank you

i have a formula below, looking up gross premiums, admin fee etc along the top row and multiple clients and product types in the columns (hence the concatenate). My source data may have the client+prod type combo more than once.

Now after learning this my formula does work, well for the few cells i've tested it for anyway......

=SUMIF(RECAP!$E$5:$E$1358,CONCATENATE($C6,LEFT($D6,6)),INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1))

however for some reason the index and match returns the column +1 is what it should do? as it hasnt done in the past when i use it with a vlookup. say if i have my match in J, it sums up K which is why i had to put in the -1.

Has anyone ever experienced this or know why its doing this as i think its correct what i've done.

Thanks in advance

Chris

 

[MikeLiberty]

Hi Chris,
I haven't run into that problem when using this combination before. The only thing that looks a little strange to me is the fact that you set the row = 0 in the index function. It looks like you're looking at a multi-row array F5:Y1358. I'm not sure what Row 0 of that would be. Maybe that's throwing you off?

INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1))


-Mike

EDIT: I just tested it and that doesnt seem to cause problems. I just hadnt used it before. My only other thought would be to check the alignment of the columns. Step through the formula and see what its returning for a column number in your match scenario. Then count over from your start column in E4 and make sure that's correct. you might just need to expand that range one column

 

[Gerald Higgins]

Hi Chris, welcome to the board.

This looks like the kind of formula that it may be possible to simplify, but it's difficult to be sure.

What's in E5:E1358 ?
What's in C6 ?
What's in D6 ?
What's in F5:Y1358 ?
What's in K5 ?
What's in E4:Y4 ?

 

[Jonmo1]

Welcome to the board...

The troublesome part is the index/match part
INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1)

The reason for the -1 is because:
The INDEX is referring to columns F:Y, but the MATCH is referring to columns E:Y
So if the value match is searching for is in say column J..
MATCH will return 6 (the 6th position in the range E4:Y4 is J4)
So the INDEX now refers to the 6th column of columns F:Y, which is column K
Hence the -1

so instead of -1, you could just change it to

INDEX(RECAP!$E$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0))


Hope that helps.

 

[chris_haynes]

Hi Chris,
I haven't run into that problem when using this combination before. The only thing that looks a little strange to me is the fact that you set the row = 0 in the index function. It looks like you're looking at a multi-row array F5:Y1358. I'm not sure what Row 0 of that would be. Maybe that's throwing you off?

INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1))


-Mike

Hi Mike

Thanks for your reply, i'm glad im not the only one confused then.

If im honest i'm not entirely sure myself in the INDEX function as this is teh first time that i have used it. To my knowledge the reason i have done this is because i have have client + product code combos appear multiple times; thats my logic for it. As there isnt a specfic row its the sum of multiple.

Chris

 

[Jonmo1]

Hi Chris,
I haven't run into that problem when using this combination before. The only thing that looks a little strange to me is the fact that you set the row = 0 in the index function. It looks like you're looking at a multi-row array F5:Y1358. I'm not sure what Row 0 of that would be. Maybe that's throwing you off?

INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1))


-Mike

The purpose of putting 0 as the row in an index is to refer to the entire column of the indexed range...Same goes for using 0 in the Column part of the index.

Example...

=INDEX(A1:C10,2,3) = C2
=INDEX(A1:C10,0,3) = C1:C10
=INDEX(A1:C10,5,0) = A5:C5

It's actually VERY usefull.

Hope that helps.

 

[MikeLiberty]

Thanks for the explanation Jon! The expertise on this board is awesome.

 

[chris_haynes]

Hi Chris, welcome to the board.

This looks like the kind of formula that it may be possible to simplify, but it's difficult to be sure.

1 What's in E5:E1358 ?
2 What's in C6 ?
3 What's in D6 ?
4 What's in F5:Y1358 ?
5 What's in K5 ?
6 What's in E4:Y4 ?

1 This is the range for the sum if, a combo of client + prod code
2 client code
3 prod code (2+3, make 1)
4 data, in this case the financials
5 heading along the top ie gross premiums, admins fees etc
6 same headings as 5 but on the source data to return the data in that column

i dont think it can be simplified but alas im new to this INDEX stuff

Welcome to the board...

The troublesome part is the index/match part
INDEX(RECAP!$F$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0)-1)

The reason for the -1 is because:
The INDEX is referring to columns F:Y, but the MATCH is referring to columns E:Y
So if the value match is searching for is in say column J..
MATCH will return 6 (the 6th position in the range E4:Y4 is J4)
So the INDEX now refers to the 6th column of columns F:Y, which is column K
Hence the -1

so instead of -1, you could just change it to

INDEX(RECAP!$E$5:$Y$1358,0,MATCH($K$5,RECAP!$E$4:$Y$4,0))


Hope that helps.

Sir you are a genious thats worked a treat. i remember having a similar problem when i first used a MATCH in a lookup but amount of reading i've done on this fried my brain this afternoon.

But its now working thank you very much.

 

[Jonmo1]

Glad to help, thanks for the feedback.