IF AND Formula

[olimajor123]

Hi,

Have a formula I think should work but it doesn't seem to be.

I basically am trying to sort some top scorers by position. I have a rank sumproduct forumla that works for the ranking, but I then want to display top scorers for each position in another sheet.

I have

<bdo dir="ltr">=if(and(Engine!$B:$B="DEF",Engine!$AQ:$AQ=1),Engine!$C:$C,"")

In the above, column B on the engine sheet displays the position, column AQ displays the rank and Column C is the players name. I am asking it to get the players name if the position is Defender and the rank is number 1. Can anyone let me know why this forumla isnt working?

Thanks
</bdo>

 

[Jonmo1]

Straight A=B comparisons only work on single cell references.
But you're using entire column references there, so Excel is using an implied reference where it only looks at the cell in that column relative to the cell you put the formula in.
=if(and(Engine!$B:$B="DEF",Engine!$AQ:$AQ=1),Engine!$C:$C,"")

So if that formula is in say Row 2, then it's really only looking at B2 AQ2 and C2
If the formula is in Row 5, then it's looking at B5 AQ5 and C5

Try this assuming there will only be 1 occurance of "DEF" and 1..

=IFERROR(LOOKUP(2,1/((Engine!$B:$B="DEF")*(Engine!$AQ:$AQ=1)),Engine!$C:$C),"")


However, I highly recommend NOT using entire column references like C:C
Keep it restricted to your actual used area
=IFERROR(LOOKUP(2,1/((Engine!$B$1:$B$1000="DEF")*(Engine!$AQ$1:$AQ$1000=1)),Engine!$C$1:$C$1000),"")

 

[steve the fish]

That would only work if your formula was housed in the row of the Engine sheet that produced the answer. It is essentially saying if housed in row 1:

=IF(AND(B1="DEF",AQ1=1),C1,"")

So you need another method such as:

=INDEX($C$1:$C$1000,MATCH(1,INDEX(($B$1:$B$1000="DEF")*($AQ$1:$AQ$1000=1),0),0))

Adjust ranges/ add sheet names to suit

 

[63falcondude]

There will only be one person with rank 1 in defense correct? If this is the case, try

Excel 2010
	A	B	C
1	Position	Player Name	Rank
2	DEF	Matthew	2
3	DEF	Mark	1
4	OFF	Luke	1
5	OFF	John	2
Engine

Excel 2010
	A	B	C
1	Position	Rank	Player Name
2	DEF	1	Mark
Sheet2

Cell Formulas
Range	Formula
C2	{=INDEX(Engine!$B$2:$B$5,MATCH(Sheet2!A2&Sheet2!B2,Engine!$A$2:$A$5&Engine!$C$2:$C$5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BarryL]

=IFERROR(LOOKUP(2,1/((Engine!$B$1:$B$1000="DEF")*(Engine!$AQ$1:$AQ$1000=1)),Engine!$C$1:$C$1000),"")

Jonmo1 , why does LOOKUP require the 1/ in the lookup_vector part?

 

[Jonmo1]

It's intentionally creating #Div/0! Errors.

The 2 criterias (Engine!$B$1:$B$1000="DEF") and (Engine!$AQ$1:$AQ$1000=1) are creating arrays of True and False Values.
Those Trues and Falses are multiplied by each other, creating 1 array of 1's and 0's

1/1 = 1
1/0 = #Div/0!

So now you have an array of {1,1,#Div/0!,1,#Div/0!,etc}
Lookup ignores errors
So
LOOKUP(2,{1,1,#Div/0!,1,#Div/0!,etc},Engine!$C$1:$C$1000)
Will match the last 1 in the array, and return corresonding value from C.

 

[olimajor123]

Ok, yes, the answer is unlikely to be housed in the same row.

There may also be instances where there are more than one ranked as number 1, in this instance is is possible to get all the names of the people ranked one into the cell??

 

[BarryL]

It's intentionally creating #Div/0! Errors.

The 2 criterias (Engine!$B$1:$B$1000="DEF") and (Engine!$AQ$1:$AQ$1000=1) are creating arrays of True and False Values.
Those Trues and Falses are multiplied by each other, creating 1 array of 1's and 0's

1/1 = 1
1/0 = #Div/0!

So now you have an array of {1,1,#Div/0!,1,#Div/0!,etc}
Lookup ignores errors
So
LOOKUP(2,{1,1,#Div/0!,1,#Div/0!,etc},Engine!$C$1:$C$1000)
Will match the last 1 in the array, and return corresonding value from C.

Brilliant thank you.

 

[steve the fish]

Barry you can see this array being formed by using the 'evaluate formula' tool on the formula tab once you have clicked into the cell housing the formula. Its very useful at times to see what you are doing.

 

[BarryL]

Barry you can see this array being formed by using the 'evaluate formula' tool on the formula tab once you have clicked into the cell housing the formula. Its very useful at times to see what you are doing.

very handy. thanks a lot