An array formula to lookup two values and produce a total score

**t_e_k** · 11-19-2009, 06:28 AM

Hello all,

I have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.

There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.

Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.

Many thanks,

Tom

**DonkeyOte** · 11-19-2009, 06:35 AM

Based on your file

Please Login or Register  to view this content.

**t_e_k** · 11-19-2009, 06:46 AM

Hi DonkeyOte,

Thanks for the very quick response!

This forumla is giving me values of TRUE in the score column C. (I didnt even know SUMPRODUCT could return a TRUE?)

Anyway, what I was actually hoping for was the summed scores as shown in column H. So;

C2 = 26
C3 = 4
C4 = 30

Thank you.

**DonkeyOte** · 11-19-2009, 06:47 AM

The formula provided, when entered into C2 (and copied down) generates 26,4,30 respectively (else I would not have posted it).

**t_e_k** · 11-19-2009, 06:52 AM

Sorry, I though I must not have explained myself clearly!

(I had managed to paste the formula into the cell twice. Oops.)

Thanks for your help.

**t_e_k** · 11-19-2009, 07:01 AM

Amazingly neat solution by the way, I am still trying to get my head round using arrays and SUMPRODUCT. I think I will read through some of the links in your signature.

Thanks.

**DonkeyOte** · 11-19-2009, 07:08 AM

Bob Phillips' white paper (Sumproduct link) is an excellent tutorial on the function (the best around IMO) and also elaborates on coercion etc...

Regards your earlier point:

Originally Posted by t_e_k

VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected.

Correct, VLOOKUP won't work ... an alternative is to use LOOKUP but this would fail to work correctly if either

a) the data was not sorted (ascending)

OR

b) any given criteria value was not listed in the lookup range (eg your blanks)

on that basis the SUMIF is therefore the simpler approach given it will handle both unsorted lookup range and simply return 0 for no match (as opposed to error).