Lookup function help

[Moses Kitimbo]

Hello,
My name is Moses Kitimbo, I'm a teacher of Applied ICT,
using the worksheet "question" Enter formulae in cells C2:C10 that refer to worksheet "Book Orders" and display whether the Text Book is Required by the student whose Student id is entered in cell A2. student sheet is also included for reference.

The workbook has been uploaned

 

[Hui]

In C2:
=IF(SUMPRODUCT((Students!$A$2:$A$21=$A$2)*(Students!$K$2:$K$21=$B2)),"Y","N")
Copy down

But is this really what you want ?

 

[Moses Kitimbo]

Thanks Ninja, however, i realize i did not make my point elaborate. So the formula is not calculating what i need.
I have attached the excel work book with the manual input in cells: c2:c10 and d2:d10
if you can enter the correct formulas to display the manual answers , i will be glad.
see book orders2

 

[azumi]

Pls find file attach......

 

[Moses Kitimbo]

Thanks that is it.
Any alternative method combining lookup function and an if function?

 

[bobhc]

The solution posted by Azumi works, why do you insist on complicating by including an unnecessary IF statement.

 

[Hui]

Moses
You could probably do that with lookup and Ifs, but why?
There are lots of other functions that simplify the process and in the process expose you to new functions that improve your knowledge and allow you to tackle even more difficult issues in future