Alternative formula to HLookup


G

Guest

(If this thread comes up twice I apologise - but the first posting did not appear on today's listings????

Can anyone suggest an alternative formula to the Hlookup formula so I don't need to change the number of rows looked up each time I copy the formula down

The current formula works but I need it to automatically know to look at it's current row

Current formula

=HLOOKUP(EK$9,($O$11:$BN14),4)*$CZ1

EK$9 is a date entered (eg 30/5/04) to lookup within the range (O11:BN14),

Row 11 has a commencing Sunday date in each cell (a formula calculates them) eg 4/4/04, 11/4/04, 18/4/04, 25/4/04, 2/5/04, 9/5/04, 16/5/04, 23/5/04, 30/5/04, 6/6/04, 13/6/04 etc

Cell W11 contains the date 30/5/0

Rows 12-14 have activity entered randomly (eg cell W14 (the column for 30/5/04) has "1" entered

The formula is written in cell DE14, and needs to find the 30/5/04 activity ("1" in W14) and multiply it by the rate in CZ14

Any help would be greatly appreciated.
 
Ad

Advertisements

B

Bernie Deitrick

BeSmart,

(I actually knew someone named B. Smart ;-0)

Simply make the row lookup parameter dynamic: in this case, replace the
4
with
ROW()-Row($BN$10)
This expression returns a 4 for cells in row 14, 3 for cells in row 13, etc.

So your final formula is

=HLOOKUP(EK$9,($O$11:$BN14),ROW()-Row($BN$10))*$CZ14

HTH,
Bernie
MS Excel MVP

BeSmart said:
(If this thread comes up twice I apologise - but the first posting did not
appear on today's listings????)
Can anyone suggest an alternative formula to the Hlookup formula so I
don't need to change the number of rows looked up each time I copy the
formula down.
The current formula works but I need it to automatically know to look at it's current row.

Current formula:

=HLOOKUP(EK$9,($O$11:$BN14),4)*$CZ14

EK$9 is a date entered (eg 30/5/04) to lookup within the range (O11:BN14),

Row 11 has a commencing Sunday date in each cell (a formula calculates
them) eg 4/4/04, 11/4/04, 18/4/04, 25/4/04, 2/5/04, 9/5/04, 16/5/04,
23/5/04, 30/5/04, 6/6/04, 13/6/04 etc.
Cell W11 contains the date 30/5/04

Rows 12-14 have activity entered randomly (eg cell W14 (the column for 30/5/04) has "1" entered)

The formula is written in cell DE14, and needs to find the 30/5/04
activity ("1" in W14) and multiply it by the rate in CZ14.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top