Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup referencing ranges that are variable
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup referencing ranges that are variable
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. -- Thanks, Shane Devenshire "whowens" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup referencing ranges that are variable
This worked great. thanks!
"whowens" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two Variable Lookup with approximate ranges | Excel Worksheet Functions | |||
Variable cell referencing | Excel Worksheet Functions | |||
Referencing tab based on Variable | Excel Worksheet Functions | |||
Variable Cell Referencing | Excel Worksheet Functions | |||
Referencing Variable Name Worksheets | New Users to Excel |