• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HLOOKUP with formula in lookup range

I

Iris Stein

New Member
I have a table in which I store sales figures for each salesman. I am trying to create an Excel table that automatically updates with the sales figures for the past 7 days. Therefore, I created the table, but when I use HLOOKUP it isn't working. I don't know whether or not this is because of the formula in the date cells. Could someone please take a look and tell me how to fix this? Much appreciated.
 

Attachments

Hi Iris ,

You don't need the INDIRECT ; just the following will do :

=HLOOKUP($B2,Sheet2!$A$1:$Y$6,2,FALSE)

The INDIRECT is required only when what is passed to the INDIRECT function , as a parameter , is a valid range address , as a string. Thus , if you really wanted to use the INDIRECT function , you would have to use :

=HLOOKUP(INDIRECT("B2"),Sheet2!A1:Y6,2,FALSE)

but this is unnecessary and even wasteful.

Narayan
 
Thanks for your replies. The file I sent it was an example as I am working on a client's sensitive data. I am still unable to get the formula to work on the original data so I have removed all the identifying material and would really appreciate it if anyone could help me understand why it is not working on this table. Thanks in advance.
 

Attachments

Hi Iris ,

See your file.

The problem is that tables cannot have header items which are not text ; the headers in your lookup table are text , while the headers you are looking up are numeric. You therefore need to convert the numeric header on one tab into a text formatted value which will find a proper match in the other tab.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top