If Statement Formula Help

[OhYouDidntKnow]

Hello. I'm having a little bit of difficulty finding a solution to a problem and hopefully someone here can help!

I am attaching a sample sheet below.

I have a set of names in column E with their avg points column F. In Column A and B there is a list of names with a points number next to them. I am trying to find a formula to enter in cell G2 that if a name in column A matches a name in Column E, it will take the points in column B and subtract it by the avg points in column F. I am able to get a standard IF statement that will do that but it will only pull the very first result where in reality that name is listed several times throughout the list. So, I either need a formula that will post it each time in Cells G2, H2, I2, etc or a formula that can subtract the points each time in column B to the name that looksup column F and takes the overall average of that?

Hoping this makes sense. Appreciate you all very much and Happy Holidays!

[etaf]

try
=INDEX(B:B,MATCH(E2,A:A,0))-F2

to get the 2nd entry
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),2))
3rd
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),3))
NOTE the last number changes for the different lookup value

We can add an IFERROR()
and also Subtract the number

BUT in the example, I have just lookup the value to check its working as you want
then the changes are quite easy

[OhYouDidntKnow]

Thanks, etaf.

Curious, is there any way to do this where I don't have to write in a new a cell every time? For my project, I have probably 400-500 names that will each appear roughly 80-100 times, so not sure if it's optimal to do 80-100 iterations of the above formula? Ultimately, I would take one formula that will subtract each number in column B to the guys Avg PTs in column F and then take the average of all those, if that's possible? If so, I would like to be able to make each number an absolute value where there are no negatives. So If someone scores less than their avg, I don't want -2.5, I would just want 2.5.

Really hoping I am making some sense and really appreciative of your help!

try
=INDEX(B:B,MATCH(E2,A:A,0))-F2

to get the 2nd entry
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),2))
3rd
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),3))
NOTE the last number changes for the different lookup value

We can add an IFERROR()
and also Subtract the number

BUT in the example, I have just lookup the value to check its working as you want
then the changes are quite easy

[OhYouDidntKnow]

Additionally, I just tried the 2nd entry and 3rd entry and that only pulls the number in column B and isn't subtracting column B from cell F2

try
=INDEX(B:B,MATCH(E2,A:A,0))-F2

to get the 2nd entry
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),2))
3rd
=INDEX($B$2:$B$100,SMALL(IF(E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),3))
NOTE the last number changes for the different lookup value

We can add an IFERROR()
and also Subtract the number

BUT in the example, I have just lookup the value to check its working as you want
then the changes are quite easy

[etaf]

YES, as I said, we can add that later,

BUT in the example, I have just lookup the value to check its working as you want
then the changes are quite easy

once you have verified the formula is working and pulling the right value
So here is a formula that can be copied
=INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1))
and uses the Number in row1 to determine , if its the 1st,2nd, 3rd etc

It would be easier to check at least the right value is being returned 1st, 2nd, 3rd etc


Do you want B - F
OR
F - B

Subtract
=INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1)) - $F2

then IFERROR ()

=IFERROR( INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1)) - $F2 , "" )

[OhYouDidntKnow]

My apologies, I missed that.

This works great, thanks. One last thing, is there a spot in this formula we can add an ABS function to turn each number into an absolute value? So rather in cell H2 where it says -0.03, it would just be 0.03 and in I2 it would be 3.03 rather than -3.03?

Thanks again!

YES, as I said, we can add that later,

once you have verified the formula is working and pulling the right value
So here is a formula that can be copied
=INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1))
and uses the Number in row1 to determine , if its the 1st,2nd, 3rd etc

It would be easier to check at least the right value is being returned 1st, 2nd, 3rd etc


Do you want B - F
OR
F - B

Subtract
=INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1)) - $F2

then IFERROR ()

=IFERROR( INDEX($B$2:$B$100,SMALL(IF($E2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),G$1)) - $F2 , "" )

[etaf]

=iferror( abs(index($b$2:$b$100,small(if($e2=$a$2:$a$100,row($a$2:$a$100)-row($a$2)+1),g$1)) - $f2) , "" )

Reference
https://www.extendoffice.com/documen...2nd-match.html

[OhYouDidntKnow]

Thank you.

So, when I applied this over to my actual project it only seems to be working for the first 3 iterations. For the example I posted it works fine because no player showed up more than 3 times in Column A, however, in my actual project, I will have each name show up at anywhere from 80-100 times. After the it pulls the first 3 like it's supposed to, it then just starts repeat the last number in iteration #3 all the way to 100. Any ideas?

=iferror( abs(index($b$2:$b$100,small(if($e2=$a$2:$a$100,row($a$2:$a$100)-row($a$2)+1),g$1)) - $f2) , "" )

Reference
https://www.extendoffice.com/documen...2nd-match.html

[OhYouDidntKnow]

Oops, that was a fault on my end. I accidentally removed one of the beginning ($) to hold that column in place in the beginning. As I dragged it was moving to the next column to the right. Apologies there and thanks again for your patience and help. Appreciate it!

[etaf]

this is the bit that looks at row 1 and decides the number of iterations to goto
g$1

you have
1 2 3 4
in your example

I suspect you will need to extend the rows
=iferror( abs(index($b$2:$b$10000,small(if($e2=$a$2:$a$10000,row($a$2:$a$10000)-row($a$2)+1),g$1)) - $f2) , "" )
AND
also the columns
If it appears 100 times
then you would need to have 100 columns
with
1,2,3,4,5,.... etc upto 100
never tried with that many - BUT the reference show Nth values so should work
see new example

[etaf]

Ops - seen the reply as i was answering and creating the new example

[OhYouDidntKnow]

Really quality stuff. Appreciate it. Take care!

Ops - seen the reply as i was answering and creating the new example