find largest number

  • Thread starter Thread starter kevin carter
  • Start date Start date
K

kevin carter

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
 
thanks for reply
i tried the fomula below
it errors on the dollar sign
if i remove the dollar it returns N?A
 
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 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?
 
sorry pete sent reply
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?




- Show quoted text -
Click to expand...
 
sorry, my typo

--
__________________________________
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
 
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
 
Try this

=MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),INDEX(C64:BB113,MATCH(lookup_val,B64:B113,0),0))
 
Try this

=MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),INDEX(C64:BB113,MATCH(look­up_val,B64:B113,0),0))

--
__________________________________
HTH

Bob
Click to expand...
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
 
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))

--
__________________________________
HTH

Bob

Try this

=MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),INDEX(C64:BB113,MATCH(look­up_val,B64:B113,0),0))

--
__________________________________
HTH

Bob
Click to expand...
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
 
Back
Top