Lookup referencing ranges that are variable

[whowens]

I have columns of data grouped by date, and the number of names on a given
day are variable. I want to lookup the number associated with a given Name
from the previous day, and if the name did not exist on the previous day I
want to use a simple formula from data on that same line.

I think its a VLOOKUP but the lookup range would need to be variable where
the lookup range for any day is the range of rows from the previous day. In
this example, for 1/2/08, the Number for John (47) needs to be pulled from
John on 1/1. I assume it would be an if statement where if the Name occurs
on the previous day it does a VLOOKUP for the Number, and when it is a new
Name I use a formula (i.e. D3/D4)

Date Name Number
1/1/07 John 47
1/1/08 Mary 22
1/2/08 John 47
1/2/08 Bill 12
1/2/08 Sue 44

Thank You,
Bill Owens

 

[ShaneDevenshire]

Hi,

Suppose your data begins in A1 (titles) and extends down to B5 for the first
day you would not need a formula. Starting on the second day you would enter
a formula such as this (here in C4
=IF(SUMPRODUCT((A$2:A3=(A4-1))*(B$2:B3=B4))<>0,SUMPRODUCT((A$2:A3=(A4-1))*(B$2:B3=B4)*C$2:C3),$F$1/$F$2)

You didn't tell us the "simple formula" you want to use if the name is new,
I used F1/F2.

Be very careful - if two people have the same name you have a problem.

 

[whowens]

This worked great. thanks!