Help with INDEX/MATCH/IF functions

[reaper_2]

afternoon,

until yesterday morning I had no clue about index array functions. Now I have slightly more of a clue, but I still can't say I understand it fully.

I'm trying to rank sheets of raw data into tables 1 to 10. On one sheet the asrray function works, but on the other it doesn't, despite being more or less identical. I was hoping somebody can tell me what to look for/check as to why it's not working in sheet 2?

anyway, in sheet 1, the array function is as follows:

{=INDEX($B$22:$B$141,MATCH(LARGE(IF($D$22:$D$141=_
"Group A",$Q$22:$Q$141),A3),$Q$22:$Q$141,0))}

where:

B is the field I want to index,
D is the Group I am addressing,
Q is the value of I want to base the rank on
A is the rank (1, 2, 3 etc...)

as I say, this works no problem. I've checked this by pivoting the raw data and comparing it with the function result.

on sheet 2, , the function is as follows:

{=INDEX($N$21:$N$563,MATCH(LARGE(IF($O$21:$O$563=_
"Group B",$P$21:$P$563),A6),$P$21:$P$563,0))}

where:

N is the field I want to index,
O is the Group I am addressing,
P is the value of I want to base the rank on
A is the rank (1, 2, 3 etc...)

as far as I can see the two formulas are identical other than they address different groups and use different fields, but in sheet 2, all I get returned is a list of 10, identical names, which don't relate to the Group I am requesting either (i.e. I've got a list of 10, all the same, from Group C)

I'm a bit stuck, anybody got any ideas what could be causing this?

ta

 

[Andrew Poulsom]

What's in A6? Did you enter the formula in a single cell and copy it down, so that that reference changed?

 

[reaper_2]

A6 contains the number 1
A7 contains the number 2
etc...

formula was entered in cell B6, confirmed with ctrl+shift+enter, and copied down.

As I say I can't work out why it works on one sheet but not the other

 

[Andrew Poulsom]

Does it say Calculate in the Status Bar? If so press F9.

 

[reaper_2]

autocalculation is enabled, (I've tried F9 just in case, no difference)

(thanks for your help btw)

 

[Andrew Poulsom]

Your formula works for me. Can you post some sample data where it doesn't work for you?

 

[reaper_2]

Pardon my ignorance but I can't see how to attach a sample data sheet to a reply?

 

[Andrew Poulsom]

If you select 'Enhanced Interface - Full WYSIWYG Editing' in 'Message Editor Interface' under User CP|Edit Options (menu towards top of screen on Board) you can copy it in Excel and paste it into a reply. It's best to surround it with borders first.