Thursday challenge, multiple lookup question...

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi all,

I have the following formula:

=IF(C$2=" ",0,HLOOKUP(C$2,$F$2:$G$3,2)*C3)+IF(D$2="
",0,HLOOKUP(D$2,$F$2:$G$3,2)*D3)

In the following table this formula would be located in the cell that
returned =5, H and T are just names:

A B C D E F G
1
2 H T H T
3 =5 5 8 0.2 0.5

The 5 and 8 are user input, the 0.2 and 0.5 are preset multipliers. The
problem I have is that I need 100 names, so H,T,Y,GG,UU,VV... for example,
with the lookup table growing to match it, but after 20 If statements or so
I hit the 255 character limit (I can get slightly more if I use a named
range). In theory I could do it in two or more sections and sum the
results, but I also have the problem that in copying the formula down the
spreadsheet I have to manually adjust the lookup reference for every If
statement for every row (which is very tedious!).

Is there a way I can use offset or sumproduct or similar to simply say 'this
formula, X times in that direction'? I've had a look but can't get anything
that works. Or if not, a way to copy the lookups down the table and
increment the lookup reference automatically?

Cheers,

Andy
 
Andy,

You'd be better served by using a row of formulas: in C4, enter the formula

=IF(C2=" ",0,HLOOKUP(C2,$F$2:$G$3,2)*C3)

(or its equivalent with your bigger lookup table), then copy it
to the right to match the width of your user entry table.

Then sum those formulas using a simple SUM.

HTH,
Bernie
Excel MVP
 
Back
Top