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,
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:C1 13,2,FALSE),0)

--
__________________________________
HTH

Bob

"kevin carter" <kevcar40@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,
> 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

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,
> > 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 -

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

On Jul 22, 12:36*pm, kevin carter <kevca...@btinternet.com> wrote:
> 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,
> > > 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 -

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?

On 22 Jul, 14:00, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Jul 22, 12:36*pm, kevin carter <kevca...@btinternet.com> wrote:
>
>
>
> > 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,
> > > > 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 -

sorry pete sent reply
On 22 Jul, 17:57, kevin carter <kevca...@btinternet.com> wrote:
> 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?
>
> On 22 Jul, 14:00, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
> > 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
>
> > On Jul 22, 12:36*pm, kevin carter <kevca...@btinternet.com> wrote:
>
> > > 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,
> > > > > 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 -

sorry, my typo

--
__________________________________
HTH

Bob

"kevin carter" <kevcar40@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,
> > 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 -

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" <kevcar40@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,
> > > 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 -

Try this

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

--
__________________________________
HTH

Bob

"kevin carter" <kevcar40@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" <kevcar40@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,
>> > > 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 -

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 -