find largest number

[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

 

[Bob Phillips]

Isn't it just

=MAX(VLOOKUP($10,B6:C56,2,FALSE),VLOOKUP(10,B64:C113,2,FALSE),0)

 

[kevin carter]

thanks for reply
i tried the fomula below
it errors on the dollar sign
if i remove the dollar it returns N?A

 

[Pete_UK]

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

 

[kevin carter]

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?

 

[kevin carter]

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 -

 

[Bob Phillips]

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

 

[kevin carter]

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

 

[Bob Phillips]

Try this

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

 

[kevin carter]

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

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

 

[Bob Phillips]

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

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

 

[kevin carter]

Bob
Thank you very much for your time
works a treat