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" 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