Using Named Range in LOOKUP formula?

[Ed from AZ]

My LOOKUP formula currently reads:
=((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
$7)))

I would like to change this so the ranges are columns in a Named
Range. Something like:
=((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
(LOOKUP($I6,Range_Col1,Range_Col7)))

The range name will be selected in a drop-down (data validation) in
another cell. So "Range_Col1" actually needs to be referenced from
the value selected in K10.

Can this be done with formulas? Or do I need a macro?

Ed

 

[T. Valko]

Assuming your named range refers to A1:G7

You can use an expression like this:

INDEX(INDIRECT(K10),,column_number)

However, if the named range is a dynamic range this won't work.

=((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))

 

[Ed from AZ]

Perfect, Biff!! Thanks!!

Ed

Assuming your named range refers to A1:G7

You can use an expression like this:

INDEX(INDIRECT(K10),,column_number)

However, if the named range is a dynamic range this won't work.

=((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP(­$I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))

--
Biff
Microsoft Excel MVP









- Show quoted text -

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP


Perfect, Biff!! Thanks!!

Ed

 

[ShaneDevenshire]

Hi,

You might shorten this approach to something like:

=LOOKUP($I6,INDIRECT(K10),CD)-J6+LOOKUP($I6,INDIRECT(K10),CG)

You can drop the extra parenthesis. I range named your column CD, CG simply
meaning Column D, Column G.


Thanks,
Shane Devenshire