Sum a row of multiple vlookups

[jtgostars]

I have a row of mixed data that gets used elsewhere with a vlookup table. some cells are blank. I would like to place a sum of all the vlookups into a single cell. I have tried using the sumproduct (isnumber(match(index...... method but it doesn't seem to create two arrays of the same size. here is a example of tables layout:


Row of Data
A B C D E F G H I J L
1 s m l m s l s

vlookup table

A B C D
1 s 100
2 m 1000
3 l 10000

So for Row 1 I need the following result where the values in Row 1 are used to in a vlookup to get the value from column D of the vlookup table:

100+0+1000+10000+0+1000+100+10000+0+100+0=22300

thnx

[Domenic]

Try...

=SUMPRODUCT(SUMIF(N1:N3,A1:L1,O1:O3))

....where N1:O3 contains your lookup table.

Hope this helps!

In article <jtgostars.1zu5py_1134237602.3611@excelforum-nospam.com>,
jtgostars <jtgostars.1zu5py_1134237602.3611@excelforum-nospam.com>
wrote:

> I have a row of mixed data that gets used elsewhere with a vlookup
> table. some cells are blank. I would like to place a sum of all the
> vlookups into a single cell. I have tried using the sumproduct
> (isnumber(match(index...... method but it doesn't seem to create two
> arrays of the same size. here is a example of tables layout:
>
>
> _Row_of_Data_
> A B C D E F G H I J L
> 1 s m l m s l s
>
> _vlookup_table_
>
> A B C D
> 1 s 100
> 2 m 1000
> 3 l 10000
>
> So for Row 1 I need the following result where the values in Row 1 are
> used to in a vlookup to get the value from column D of the vlookup
> table:
>
> 100+0+1000+10000+0+1000+100+10000+0+100+0=22300
>
> thnx