Check who owns 13 pens

[learning_bee]

Hi I want to check which student owns 13 pens. I think it will be done using offset and match function but I am not able to use them properly.

[JohnTopley]

And expected results are ?? Confused by column H entries !

[etaf]

you have a student name
and then number of red and number of blue pens
why would they also have a number of
Does not own pen

i dont really understand your layout

example
Students name | Red Pen | Blue Pen | Does not own pen
XYZ | 25 | 3 |12

none add up to 13

so i'm a little confused

[learning_bee]

Hey I have deleted that column.. Actually some other questions were there related to that column. Now can you tell me how can we do it?

[learning_bee]

I have deleted that column. The result should be that student name who has total 13 pens(red+blue).

[HansDouwe]

One way:

Use condtional formatting on cell E5 (and down): with conditional formula =(F5+G5=13)
See attachment.

Is this what you want?

[learning_bee]

No.. I want the result in a new cell.

[JohnTopley]

This ??

in H5

=IF(SUM(F5:G5)=13,"Yes","No")

[etaf]

=index(e5:e9,match(13,f5:f9+g5:g9,0))


if 2 people have 13 - it will only return the 1st one

[learning_bee]

It is giving #N\A error.

[JohnTopley]

Try for multiple entries

=IFERROR(INDEX($E$5:$E$9,AGGREGATE(15,6,ROW($A$1:$A$100)/(($F$5:$F$9+$G$5:$G$9=13)),ROWS($1:1))),"")

[HansDouwe]

An other way (for users Excel 365 or Excel 2021):

Adding a new total colomn in column H and using the FILTER function: =FILTER(E5:H9;H5:H9=13;"No students").
It shows all students with exact 13 pens.

[learning_bee]

This formula worked:

=INDEX(E$5:E$9,MATCH(TRUE,INDEX(($F$5:$F$9+$G$5:$G$9=13),0),0))