Excel range names in lookup formulas 3

[georgewashington]

Hello,

I have a question on referring to ranges in an Excel “lookup” formula. How do I get lookup to look to a cell outside the formula cell for a range name.

Background: The horizontal look up table typically works like this: hlookup(what to look up, where to look up, how many rows to go down).

As an example, you can tell a "lookup" formula to look for value 10 in range "infolocation" and then go 100 rows down to grab a value. you can do this like so: =hlookup(10,infolocation,100). This works if “infolocation” is a current range name in the spreadsheet.

Or you can tell the formula to look for value 10 in range D5:J15 and go 100 rows down to grab a value. Like so: =hlookup(10,D5:J15,100) works if because D5:J15 is a valid what to tell a look up formula.

But if you wanted to tell the lookup formula to go to cell, say B4, to get the range name (rather than cell B4 being the range) it would look like this: =hlookup(10,B4,100). (Assuming one types “infolocation” into cell B4.) But it does not work. Excel thinks cell B4 is the range to look in - not the cell that cantains a range name.

How do I get a lookup formula to grad a range name outside the formula per se?

Thanks for any help - this is really bugging me,

georgewashington

 

[smozgur]

This should work if B4 exactly has the range name as text even as a result of another function.

=hlookup(10,INDIRECT(B4),100)

Regards
Oz

 

[Zathras]

You can use INDIRECT, for example if cell I2 contains the text 'D5:J15 then instead of

=hlookup(10,D5:J15,100)

you could get the same result with

=hlookup(10,INDIRECT(I2),100)

Incidently, unless you really want to find near values in a sorted table (e.g. a tax table or commission schedule), you should probably specify the fourth parameter as 0 or False. Even if you are using a table lookup, it would be preferable to explicitly state 1 or True to avoid questions later.

 

[DaleWatson123321]

George,

In addition to what has already been suggested, here are a couple of other "pointers".

First the example formula:
=HLOOKUP(col,INDIRECT(tbl),row,FALSE)

1) Notice that I've used range names (like in your example).

By using a range name for cell used with the INDIRECT function (I've named this cell "tbl&quot you won't have to be concerned with having to change the formula if-and-when you make changes to your worksheet such as inserting or deleting rows or moving data.

In the cell named "tbl", you should of course enter other range names (as text) to reference one or more different tables. For example, you could use range names like "tbl_1" and "tbl_2".

2) In your example, you've "hard coded" (within the formula itself) the "lookup value" and the "row".

As an alternative, you could (also) use range names for the "lookup value" and the "row". You'll notice in my example, I've used "col" as a range name for the "lookup value", and the name "row" for the row to be used in the lookup.

By having these other "variables" in their own cells, it normally means "more flexibility" in terms of being able to "easily" change the values - by simply entering the values into those separate cells.

I hope this helps. Please advise as to how this "fits" - i.e. does it all make sense ?

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[georgewashington]

Thank you gentlemen, these are great suggestions...