Lookup function

[Jan Kronsell]

I have the following data

A B C
School Student Value
My school Peter 5
John 6
Anne 7
...
...
Your School Bill 8
Peter 7
Joan 10
...
No School Bill 11
John 3
Anne 7

The number of students per school varies from a few to several hundred. Is
it possible, using only a formula (no VBA) to return the value of a certain
student of a certain school?

If the schoolname were repeated for each student, it could be done using:

=SUMPRODUCT((A2:A7000=D1)*(B2:B7000=D2)*(C2:C7000)) where D1 is the name of
the school, and D2 the name of the student.

This is something I try help a colleague with, and she tells me, that the
data cannot be changed to repeat schoolname.

Jan

 

[Bernard Liengme]

So if you cannot change column A, let's work with another column.
In D2 enter =IF(ISBLANK(A2),A1,A2) and copy down to the end by double
clicking the fill handle (solid square in lower right corner of active cell)
Now Cut and Past D27000 over to another (unused) column, let's say N
Column N could even be hidden if you wish

Then we can use you formula (with minor modification)
=SUMPRODUCT((N2:N14=D1)*(B2:B14=D2)*(C2:C14))
I used 14 for testing, you need 7000

best wishes

 

[Jan Kronsell]

Thank you. I will ask her, if she can modify other columns.

Jan

 

[dranon]

Instead of =IF(ISBLANK(A2),A1,A2) use =IF(ISBLANK(A2),D1,A2)

 

[Jan Kronsell]

With a small change i got it to work.

=IF(ISBLANK(A2);D1;A2)

Your solution repeated the scoolname once, the returned zeros. But thanks
for the idea.

Jan