Index and Match formula assistance

[bigroo1958]

Hi all,

I am looking for some assistance as to why this index and match formula is not work.
Here is what I want to do:


I want to match the month in Column A, then match the Lid in Column B and fill in the Uptime from Column J for each month under column C.

Not too sure why my formula will not work.

Thanks in advance!!

[daddylonglegs]

Perhaps it would be easier with SUMIFS, i.e. this formula in C2 copied down

=SUMIFS(J:J,H:H,A2,I:I,B2)

[newdoverman]

Something wrong with downloaded file at my end

Got it sorted. Avoid using full column references as they cause unnecessary calculation.

Array enter (Ctrl + Shift + Enter) this formula in C2 and copy down

=INDEX($J2:$J100,MATCH(A2&B2,H2:H100&I2:I100,0))

[bigroo1958]

Oh, interesting. What error message are you getting?

[ConneXionLost]

If you need to match the month value and not the actual dates, then try this array formula:

=INDEX($J:$J,MATCH(MONTH(A2)&B2,MONTH($H:$H)&I:I,0),1)

Confirm as an array with Ctrl-Shift-Enter.

Cheers,

[bigroo1958]

Thanks to those that replied and left a solution, also thanks to those that stopped by as well.

DaddylongLegs,
Thanks, I did not think of sumifs function, but it worked. I was thinking lookup value so settled on these functions. I thought sumif was for calculations, but worked in this example.

Newdoverman,
Its strange, your formula worked for my example, but when applied to my real list data of 2700 rows, I did not get the correct values returned. I will look further

ConneXionLost,
Your formula worked like a charm, thanks!!
I noticed you using the Ctrl+Shift+Enter to insert the formula, when do you use this versus just plain enter?

[ConneXionLost]

You need to use the Ctrl-Shift-Enter when you are using an array formula.

[newdoverman]

Notice that I limited the range to row 100. Increase that and enter with Ctrl + Shift + Enter

=INDEX($J2:$J3000,MATCH(A2&B2,H2:H3000&I2:I3000,0))

[ConneXionLost]

If you are seeing errors, one possibility is the "dates" you have in columns A and H are actually text.

[bigroo1958]

Great, thanks to our Canadian friends, I have this working as needed!!
Thanks all!