| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP using variable columns
I'm trying to reference a variable column rather than a specific column # in
my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLOOKUP using variable columns
I am confused! You are using HLOOKUP but talk about a variable COLUMN
Is this a VLOOKUP or is it the ROW that varies? It is perfectly OK to use a cell reference rather than a number literal in the syntax of either function I would help if we had a better idea of the actual data. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chuka" wrote in message ... I'm trying to reference a variable column rather than a specific column # in my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLOOKUP using variable columns
Thanks for your timely response. Yes the row varies based upon the date.
How do I write a formula for a variable row? Sorry I think I got my VLOOKUP & HLOOKUP mixed. When I attempted to reference a cell =HLOOKUP(B3,$B$6:$D$7,?,FALSE) I had trouble. Any ideas how to correct this without including a whole number for the row? "Bernard Liengme" wrote: I am confused! You are using HLOOKUP but talk about a variable COLUMN Is this a VLOOKUP or is it the ROW that varies? It is perfectly OK to use a cell reference rather than a number literal in the syntax of either function I would help if we had a better idea of the actual data. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chuka" wrote in message ... I'm trying to reference a variable column rather than a specific column # in my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLOOKUP using variable columns
You should be able to use =HLOOKUP(B3,$B$6:$D$7,D5,FALSE)
with D5 holding a valid row number. Since the range is B6:D7 the only possible values are 1, and 2; i.e. the first or second row of B6:D7 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chuka" wrote in message ... Thanks for your timely response. Yes the row varies based upon the date. How do I write a formula for a variable row? Sorry I think I got my VLOOKUP & HLOOKUP mixed. When I attempted to reference a cell =HLOOKUP(B3,$B$6:$D$7,?,FALSE) I had trouble. Any ideas how to correct this without including a whole number for the row? "Bernard Liengme" wrote: I am confused! You are using HLOOKUP but talk about a variable COLUMN Is this a VLOOKUP or is it the ROW that varies? It is perfectly OK to use a cell reference rather than a number literal in the syntax of either function I would help if we had a better idea of the actual data. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chuka" wrote in message ... I'm trying to reference a variable column rather than a specific column # in my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLOOKUP using variable columns
Hi,
The real key hear is to decide on how you want to determine the varialble row number. You say you want it to be a variable, but you don't tell us what determines what the row number should be? Thanks, Shane -- If this helps, please click the Yes button Cheers, Shane Devenshire "Chuka" wrote: I'm trying to reference a variable column rather than a specific column # in my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLOOKUP using variable columns
Hear, here, Shane ! <gr
-- Bernard "Shane Devenshire" wrote in message ... Hi, The real key hear is to decide on how you want to determine the varialble row number. You say you want it to be a variable, but you don't tell us what determines what the row number should be? Thanks, Shane -- If this helps, please click the Yes button Cheers, Shane Devenshire "Chuka" wrote: I'm trying to reference a variable column rather than a specific column # in my HLOOKUP formula. A1 B1 C1 D1 A2 1/6/2009 Today's Date A3 1/6/2009 Start Date A4 A5 1/5/2009 1/6/2009 A6 1/5/2009 1 0 A7 1/6/2009 2 1 I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the ? I've tried to reference a cell that contained a whole number but that didnt work. I want to be able to copy and paste this formula for several different individuals based upon their starting date & not necess. column D every time. Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to sum columns by variable criteria | Excel Discussion (Misc queries) | |||
| HLOOKUP using multiple columns | Excel Discussion (Misc queries) | |||
| Graphing Variable-length columns | Charts and Charting in Excel | |||
| Hlookup in other columns | New Users to Excel | |||
| How to see columns from which values were not returned by HLOOKUP functions? | Excel Worksheet Functions | |||
Linear Mode
