SUMPRODUCT with Text result

[Steven Bitaxi]

I have a spreadsheet with a few thousand entries - school, address,
board of ed - and another in the same workbook with a list of about 100
schools with just the name and board of ed. I want to get the address
information for each of these schools without looking them up manually.
I know the information is consistent so I was trying sumproduct.
Everything was fine until I tried to get a text result. I get a #VALUE
error.

=SUMPRODUCT(--(Board=A2),--(School=B2),(Address))

I have named the ranges in the other worksheet to make the formula
cleaner, but am not sure how to get it to display the text of Address.

 

[Earl Kiosterud]

Steven,

It sounds as if you can use VLOOKUP.

Earl Kiosterud
www.smokeylake.com

 

[Steven Bitaxi]

Thanks. It looks like VLOOKUP was just the thing. Took me a little
research to figure it out, but the direction was right.


Much appreciated Earl!

 

[RagDyer]

You might try this *array* formula:

=INDEX(address,MATCH(1,(board=A2)*(school=B2),0))