HLOOKUP problem, help appreciated

[Mark Stephens]

I am using hlookup to locate a value in a spreadsheet. Here is the function
typed into the cell:

=MATCH(MAX(Sheets(1)!A:A),Sheets(2)!A1:H1000,0)

If I type in the row number instead of the max function then I get the
correct value so what's happening is I am getting the value contained in the
cell (maximum value) of column A rather than the row value.

I think I should possibly use the match function instead but that wont work
either so how do I return the row number containing the maximum value in
column A of my sheet containing the array rather than the actual value
(Address?).

Any help much appreciated.

Kind regards, Mark

 

[STEVE BELL]

Mark,

Your formula
=MATCH(MAX(Sheets(1)!A:A),Sheets(2)!A1:H1000,0)
is looking for the row number on Sheets(2) that contains an exact match to
Max(Sheets(1)!A:A),
only if Sheets(2) contains that value.

Also - formulas don't work with "Sheets(1)" & "Sheets(2)" (those work in
code)
Replace them with the actual sheet name, Like "Sheet1" or "Sheet2" or
"MySheet" or ....