Problems with combining HLOOKUP with INDIRECT

Hallo Spalton,

You need to save the search area under a name area. This you can find in the menu bar.
Then you can refer to it using direct.

named area= A8:U10
field F7 = name of the area.

Try your formula again, without only indirect(F7) and it would work.

Harm

No naming needed,

=HLOOKUP($C$11,INDIRECT("'"&F7&"'!A8:U10"),3,FALSE)

Cheers Jason, that worked straight away! Much appreciated mate.

I'm trying to lookup a list of tax ID numbers from other sheets within my workbook and pull them into a summary tab. So the column that I'm trying to set up is labelled as Parcel # and all the subsequent columns in the other tabs from which I'm trying to pull from, are also labeled Parcel #. My ideal scenario is being able to combine an Hlookup function (so that I can copy the formula down a column to bring in the rows of other information) with an indirect function (to look in the other sheets within my same workbook) but using the formulae suggested above. I keep getting an #N/A message.

Alternatively, I have been using just a standard Indirect function, INDIRECT("'"&$B$2&"'!"&"A1:a500"), and copying that down in my summary column and that pulls in the information ok. So perhaps I don't need the Hlookup? However, I have to make sure that every sheet in my workbook is structured the same way (i.e. my list of parcel #'s start on row 4 in every sheet).

I am using however, this Vlookup function (below) to pull in information from other tabs, into my summary tab, from across columns and the Vlookup is working just fine with the indirect function:

IFERROR(VLOOKUP($A6,INDIRECT("'"&$B$2&"'!"&"A1:z500"),MATCH($E$2,Crosswinds!$A$2:$ZZ$2,0)+1,FALSE),0)

I just don't know why my Hlookup function is not pulling in anything.