resize range formula

[richfm1]

Hi,
is there a way to resize the range within a vlookup? the range of data shifts columns to the right each month data is entered and the subsequent rows of data can increase or decrease. the vlookup range would need to change each month to capture the criteria within the new range

thanks for the help

 

[njimack]

Take a look at dynamic ranges: http://contextures.com/xlNames01.html#Dynamic

 

[Excelestial]

Name the ranges using the OFFSET function. I'm not sure if you're wanting to make dynamic the width (number of columns) or length (number of rows). An example of this offset formula is =OFFSET($A$1,0,0,countif($A$1$A$500,">"""),1).

This interprets as...your growing range is in column A beginning in Cell A1 and so you will reference this first cell. You will then tell it to offset it by 0 rows and 0 columns for a length to accomodate however many nonblank cells in the range of A1:A500 for a single (1) column only. The trick is...if there are blank rows in between records, your resulting list can come up short as the formula is only sizing to the "number" of non-blank rows and not the "size" of all the rows.

 

[richfm1]

helpful but not quite what i need... data is not static

ex:
a3 = 01/31/11 & a10 = acc#4 & b10 = 100
c3 = 02/28/11 & c20 = acc#4 & d10 = 125
e3 = 03/31/11 & e110 = acc#4 & f10 = 50
g3 = 04/30/11 & g5 = acc#4 & h10 = 150

if a2= date required i need the value associated with said date and acc#

 

[Excelestial]

This really isn't an expanding range problem...although you can still calculate within a named range (must include the name of the range in the formula). Seems to me that you'll want to tweek your VLOOKUP range to be all exhaustive and set to the limits of the maximum range of data you might get month to month.

 

[richfm1]

perhaps an easier way... if i specify the column number how can I determine the row number for a value. ex: column 37 has acc#4 in row 60... is there a formula that can determine the row using the column?

 

[Excelestial]

by using the MATCH function paired with either VLOOKUP, HLOOKUP, INDEX, or OFFSET.