Lookup function

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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
 
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 D2:D7000 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
 
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
 
Back
Top