Variable Range

[sep1280]

Hi,
I am using VLOOKUP functions throughout my workbook that looks at the
last worksheet which contains equipment costs. An example is:

=IF(B30<>"",IF(ISERROR(VLOOKUP(B30,'Equipment
Costs'!$A$2:$D$895,4,FALSE)),"No Match",VLOOKUP(B30,'Equipment
Costs'!$A$2:$D$895,4,FALSE)),0)

The problem is that I often update my equipment costs worksheet, which
then could grow or decrease in size. Then the range for the hundreds
of VLOOKUP functions are incorrect. Is there a way instead of using
the range 'Equipment Costs'!$A$2:$D$895, I could change the formula to
one cell that will define the range? When I type a cell reference in
the VLOOKUP function, it won't use that cells value for the range.
Excel is looking at it as the range. Any ideas? Thanks in advance!
Scott

 

[JE McGimpsey]

Since you're using the exact match switch, you can probably use the
entire column:

=IF(B30<>"",IF(ISNA(MATCH(B30, 'Equipment Costs'!$A:$A, FALSE)), "No
Match", VLOOKUP(B30, 'Equipment Costs'!$A:$D, 4, FALSE)), 0)

 

[Biff]

Hi!

A couple of options.

If there is no data below your lookup table just use entire columns as range
references:

VLOOKUP(B30,'Equipment Costs'!$A:$D,4,FALSE))

Or use dynamic ranges (ranges that automatically adjust as data is
added/deleted). See instructions here:

http://contextures.com/xlNames01.html#Dynamic

Biff

 

[Guest]

Hello sep1280,

If you name your ranges instead of refering to them by their cell references
you should be able to add to your original data.

Highlight A2895 and press Ctrl + F3. Give the range a name eg.
EquipmentCosts (no spaces allowed).

Your formula will then be:

=IF(B30<>"",IF(ISERROR(VLOOKUP(B30,EquipmentCosts,4,FALSE)),"No
Match",VLOOKUP(B30,EquipmentCosts,4,FALSE)),0)

As long as you insert your new cost rows between the first and last row of
the range the formulas will update. OR you could make the range bigger than
it is to leave room for extra cost rows.

Judith