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(C6 4:BB113,MATCH(lookup_val,B64:B113,0),0),0))

--
__________________________________
HTH

Bob

"kevin carter" <kevcar40@btinternet.com> wrote in message
news:9466e15f-b292-4175-be3c-7cdcafc084b0@t54g2000hsg.googlegroups.com...
On 23 Jul, 09:06, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Try this
>
> =MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),I NDEX(C64:BB113,MATCH(look*up_val,B64:B113,0),0))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "kevin carter" <kevca...@btinternet.com> wrote in message
>
> news:1012391a-9164-4648-b41f-1bfc9d60c63c@p25g2000hsf.googlegroups.com...
>
>
>
> >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 wrote:
>
> >> sorry, my typo
>
> >> --
> >> __________________________________
> >> HTH
>
> >> Bob
>
> >> "kevin carter" <kevca...@btinternet.com> wrote in message
> >>news:9e2c3b39-5340-4aa8-a3cb-6177a5979084@l42g2000hsc.googlegroups.com...
> >> 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
>
> >> > =MAX(VLOOKUP($10,B6:C56,2,FALSE),VLOOKUP(10,B64:C1 13,2,FALSE),0)
>
> >> > --
> >> > __________________________________
> >> > HTH
>
> >> > Bob
>
> >> > "kevin carter" <kevca...@btinternet.com> wrote in message
>
> >> >news:77b0879c-a548-4715-b069-c9f2495b2f52@z66g2000hsc.googlegroups.com...
>
> >> > > 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,R6 4: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,
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
> >> > > the values in the cells and return the largest
>
> >> > > any ideas
>
> >> > > thanks
>
> >> > > kevin- Hide quoted text -
>
> >> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Bob
Thank you very much for your time
works a treat

On 24 Jul, 10:49, "Bob Phillips" <Bob...@somewhere.com> wrote:
> 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(C6 4:BB113,MATCH(lookup_val,*B64:B113,0),0),0))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "kevin carter" <kevca...@btinternet.com> wrote in message
>
> news:9466e15f-b292-4175-be3c-7cdcafc084b0@t54g2000hsg.googlegroups.com...
> On 23 Jul, 09:06, "Bob Phillips" <Bob...@somewhere.com> wrote:
>
>
>
> > Try this
>
> > =MAX(INDEX(C6:BB56,MATCH(lookup_val,B6:B56,0),0),I NDEX(C64:BB113,MATCH(look**up_val,B64:B113,0),0))
>
> > --
> > __________________________________
> > HTH
>
> > Bob
>
> > "kevin carter" <kevca...@btinternet.com> wrote in message
>
> >news:1012391a-9164-4648-b41f-1bfc9d60c63c@p25g2000hsf.googlegroups.com....
>
> > >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 wrote:
>
> > >> sorry, my typo
>
> > >> --
> > >> __________________________________
> > >> HTH
>
> > >> Bob
>
> > >> "kevin carter" <kevca...@btinternet.com> wrote in message
> > >>news:9e2c3b39-5340-4aa8-a3cb-6177a5979084@l42g2000hsc.googlegroups.com...
> > >> 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
>
> > >> > =MAX(VLOOKUP($10,B6:C56,2,FALSE),VLOOKUP(10,B64:C1 13,2,FALSE),0)
>
> > >> > --
> > >> > __________________________________
> > >> > HTH
>
> > >> > Bob
>
> > >> > "kevin carter" <kevca...@btinternet.com> wrote in message
>
> > >> >news:77b0879c-a548-4715-b069-c9f2495b2f52@z66g2000hsc.googlegroups.com...
>
> > >> > > 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,R6 4: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,
>
> 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
> > >> > > the values in the cells and return the largest
>
> > >> > > any ideas
>
> > >> > > thanks
>
> > >> > > kevin- Hide quoted text -
>
> > >> > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -