On May 30, 7:50 pm, Kelly wrote:
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

"vezerid" wrote:
On May 30, 7:11 pm, Kelly wrote:
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

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