Sum Multiple VLOOKUPS

[Dan]

All,

I am trying to summarize the results of multiple VLOOKUPS
in a single cell.

Current Approach:
=VLookup(A1,NamedRange",2,0)+VLookup(A2,NamedRange",2,0)
+VLookup(A3,NamedRange",2,0)+etc.

You can see why I would like to find a better approach.
Any help would be greatly appreciated.

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(ISNUMBER(MATCH(index(namedrange,,1),A1:A5,0))),index(nam
edrange,,2))

 

[Don Guillett]

try this idea where b1:b3 is the namedrange,1 column and c1:c3 is ,2
=SUMPRODUCT((B1:B3=A1:A3)*C1:C3)

 

[Guest]

Frank,

The formula is doing exactly what I asked, although I on't
completely understand it. But I forgot a factor in my
original question. Hopefully it will be an easy addition
to the formula you already provided.

Here is what I have set up.

In Column A2 throught A50 are employee names. Columns B2 -
B5 contain the employees hours for January. Columns C2 -
C50 contain their hours for February etc.

I have a named range called "Rates" which is a two column
array containing everyones pay rate.

In Row 51 I would like to have a summary line which totals
the Dollars spent for that month.

Example

A B C D RATES
1 Ted 10 50 44 TED $10
2 Jane 20 55 40 Jim $9
3 Bill 10 50 50 Mary $12
4 Mary 20 50 40 Bill $16
~
50 Jim 20 50 44 Jane $17
==========================
51 $1020


Currently the formula you gave me before returns the sum
of the rates. I need it to return the sum of the rates
times the hours worked.

Thank You very much

 

[Aladin Akyurek]

Sort Rates in ascending order on its first column, then use for January:

=SUMPRODUCT(LOOKUP($A$2:$A$50,Rates),B$2:B$50)

Copy the formula across for the other months.

....

Here is what I have set up.

In Column A2 throught A50 are employee names. Columns B2 -
B5 contain the employees hours for January. Columns C2 -
C50 contain their hours for February etc.

I have a named range called "Rates" which is a two column
array containing everyones pay rate.

In Row 51 I would like to have a summary line which totals
the Dollars spent for that month.

Example

A B C D RATES
1 Ted 10 50 44 TED $10
2 Jane 20 55 40 Jim $9
3 Bill 10 50 50 Mary $12
4 Mary 20 50 40 Bill $16
~
50 Jim 20 50 44 Jane $17
==========================
51 $1020

....

 

[Frank Kabel]

Hi Aladin
nice one

 

[Dan]

That would work great but I do not know what order the
names will be entered in the workbooks. The workbook
allows the user to pick and choose names from a list,
depending on who will be working on the job.

 

[Aladin Akyurek]

The sorting involves only the Rates range, not the data range.