HLOOKUP second column

[Kelly]

I am using the following macro to gather the information that I need:
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29,FALSE)

When I change the formula to
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29,FALSE)
to gather data from the 3rd column of the data area, I get a #NA

I made sure to copy the lease number (the A380 reference) to each column
(D2, F2, H2) on the worksheet I'm looking up.

Can you please tell me what's wrong with the second macro? And why I'm
getting the #NA response?

thanks
Kelly

 

[vezerid]

I am using the following macro to gather the information that I need:
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29,FALSE)

When I change the formula to
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29,FALSE)
to gather data from the 3rd column of the data area, I get a #NA

I made sure to copy the lease number (the A380 reference) to each column
(D2, F2, H2) on the worksheet I'm looking up.

Can you please tell me what's wrong with the second macro? And why I'm
getting the #NA response?

thanks
Kelly

Kelly,
your formula seeks the sheet named in A380 and then goes to the range
D2:H104 and does the HLOOKUP there to find what is in row 30 of the
column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require
the lookup value to appear in the first row (or column respectively)
of the lookup table. If you change your reference to F2:H104 HLOOKUP
will not be able to find the lookup value.

HTH
Kostis Vezerides

 

[DazzaData]

Have you got your rows and columns mixed up.

This formula looks along a row and then once it finds the column with that
value down 28 rows to return a value.

incidentally I think offset indirect would do the job more easily

offset( INDIRECT("'"&$A380&"'!D2") ,28,0 )
offset( INDIRECT("'"&$A380&"'!f2") ,28,0 )
offset( INDIRECT("'"&$A380&"'!H2") ,28,0 )

since the formula use a380 to select sheet it isn't really using it to
lookup anything inside the sheet, why would there be a sheet named after a
lease number with data about other lease numbers in it??

 

[Kelly]

I made sure to put the Lease number (A380 reference) at the top of the column
in F2 (as it is in D2). Can't I start my lookup from column F instead of
column D? And, if not, how do I look up in the range D2:H104 and return the
data in columns F or H?

Thanks for your quick response!
Kelly

 

[DazzaData]

I am not sure why the HLOOKUP is not functioning.

But what I would reiterate is that it is not being used for its intended
purpose here and that is part of the problem.

If you want a formula that goes to a sheet (name variable) and collects data
from a particular cell D30,f30,H30 ,

then offset( INDIRECT("'"&$A380&"'!D1") ,29,0 )

has several advantages but the main one is it will not give errors unless
you give it a leasenumber for a sheet that doesn't exist

Trust me this will help!

 

[vezerid]

I made sure to put the Lease number (A380 reference) at the top of the column
in F2 (as it is in D2). Can't I start my lookup from column F instead of
column D? And, if not, how do I look up in the range D2:H104 and return the
data in columns F or H?

Thanks for your quick response!
Kelly

It seems to me you need VLOOKUP not HLOOKUP for the data you are
describing. If you want you can email me the file (vezerid at act dot
edu). Most likely I will reply tomorrow as I am soon leaving, but
someone else might jump in. In the meanwhile try to decipher the
online help pages on VLOOKUP and HLOOKUP and see which one fits your
task.

Regards,
Kostis Vezerides

 

[Kelly]

When I correct the formula you indicated (adding = before offset and
eliminating unnecessary spaces), I get the info in column D on the worksheet,
not column F as I wanted. If I change your formula to be
=offset(INDIRECT("'"&$A380&"'!F1"),29,0) I get a #REF! response.
I still must be doing something wrong.

 

[DazzaData]

If it works for d1->d30 but not when you change d1 to f1, my first thought
would be #ref! must be whats actually in f30 which might have been the
problem all along