This arose today when I was helping someone here. I am confused.... Any explanations on offer????
This is a standard formula used to return the maximum SUM in a column with one criterion:
=IFERROR(1/(1/LARGE(INDEX(SUMIFS(C3:C10,A3:A10,"A",B3:B10,B3:B10),0),1)),"")
and this returns the corresponding value in another column.
=IFERROR(INDEX(B3:B10,MATCH(F4,INDEX(SUMIFS(C3:C10,A3:A10,"A",B3:B10,B3:B10),0),0)),"")
Refer to the file. Blocks A, B, C and D are EXACTLY the same data/formulae, with the sole exception that the values for Joe (the bloke with the highest SUM value) vary. The values increase from left to right. One 3/4 occasions Joe's name is returned correctly.
Block A, it works.. Block B, it works, Block C... it doesn't (!!!), Block D it starts working again. In block E, I found that a 1/(1/SUMIFS) fixed it and Joe's name is, once again, returned.
I have been travelling for a couple of days and I'm tired and a bit grumpy. I cannot see why this is failing in Block C and it's beginning to annoy me. Does anyone know why Block C has failed?
Bookmarks