 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2008find largest number
hi, i have a spreadsheet containing 2 grids of data relating to an op the grid range for grid1 is B6:bb56 grid 2 is B64:BB113 in a third grid i have this formula (MAX(VLOOKUP($B$6,R6:R56,2,FALSE),VLOOKUP($B$64,R64:R113,2,FALSE))),0) i am trying to search a column for a fault ie op 10 which is in colulm B in both grids, when i find the match in both grids i want to look at a range of cells and compare the values in the cells and return the largest any ideas thanks kevin Isn't it just =MAX(VLOOKUP($10,B6:C56,2,FALSE),VLOOKUP(10,B64:C113,2,FALSE),0)  Signature__________________________________ HTH Bob > hi, > i have a spreadsheet containing 2 grids of data relating to an op [quoted text clipped - 17 lines] > > kevin thanks for reply i tried the fomula below it errors on the dollar sign if i remove the dollar it returns N?A > Isn't it just > [quoted text clipped - 29 lines] > > - Show quoted text - It might be that the 10 you are looking for is actually a text value in your grids (most people call them tables) - try it like this: =MAX(VLOOKUP("10",B6:C56,2,FALSE),VLOOKUP("10",B64:C113,2,FALSE),0) Hope this helps. Pete > thanks for reply > i tried the fomula below [quoted text clipped - 40 lines] > > - Show quoted text - thanks bob but will that formula work when the text is in column be and the numeric valus in column S or any column up to bb? > It might be that the 10 you are looking for is actually a text value > in your grids (most people call them tables) - try it like this: [quoted text clipped - 51 lines] > > - Show quoted text - sorry pete sent reply > thanks bob > but will that formula work when the text is in column be and the [quoted text clipped - 57 lines] > > - Show quoted text - sorry, my typo Signature__________________________________ HTH Bob thanks for reply i tried the fomula below it errors on the dollar sign if i remove the dollar it returns N?A On 22 Jul, 11:17, "Bob Phillips" <Bob...@somewhere.com> wrote: > Isn't it just > [quoted text clipped - 33 lines] > > - Show quoted text - I may not have explained correctly i have a two tables of data table 1 B6:bb56 table 2 B64:bb113 the op numbers are in column B the numeric values are in columns c to bb the formula provided works fine with column C but fails on columns D to BB How do i modify to formula to find the op in column b and compare the values in columns c then columns d etc.. thanks kevin > sorry, my typo > [quoted text clipped - 47 lines] > > > > - Show quoted text - Try this =MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),INDEX(C64:BB113,MATCH(lookup_val,B64:B113,0),0)) Signature__________________________________ HTH Bob >I may not have explained correctly > i have a two tables of data table 1 B6:bb56 [quoted text clipped - 62 lines] >> > >> > - Show quoted text - > Try this > [quoted text clipped - 61 lines] > >> > > colulm > >> > > B in both grids, Thanks Bob Works a treat one question how can i stop N/A if the op is not in one of the tables i tried iserror but i am failing thanks kevin > >> > > when i find the match in both grids i want to look at a range of > >> > > cells and compare [quoted text clipped - 9 lines] > > - Show quoted text - If all values are positive, try =MAX(IF(ISNUMBER(MATCH(lookup_val,B6:B56,0)),INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),0), IF(ISNUMBER(MATCH(lookup_val,B64:B113,0)),INDEX(C64:BB113,MATCH(lookup_val,B64:B113,0),0),0)) Signature__________________________________ HTH Bob On 23 Jul, 09:06, "Bob Phillips" <Bob...@somewhere.com> wrote: > Try this > [quoted text clipped - 66 lines] > >> > > colulm > >> > > B in both grids, Thanks Bob Works a treat one question how can i stop N/A if the op is not in one of the tables i tried iserror but i am failing thanks kevin > >> > > when i find the match in both grids i want to look at a range of > >> > > cells and compare [quoted text clipped - 9 lines] > > - Show quoted text - Bob Thank you very much for your time works a treat > If all values are positive, try > [quoted text clipped - 107 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.