Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HLOOKUP problem, help appreciated
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HLOOKUP problem, help appreciated
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 .... -- steveB Remove "AYN" from email to respond "Mark Stephens" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLOOKUP PROBLEM | Excel Worksheet Functions | |||
hlookup problem | Excel Discussion (Misc queries) | |||
.. any help appreciated .. i think it's a pasting HTML problem. | Excel Discussion (Misc queries) | |||
Thorny VBA problem. Any creative solution appreciated. | Excel Programming | |||
Stubborn Link Data/Value Problem - Help Appreciated! | Excel Programming |