Lookup help (simple)

Hello all,

I am using the Lookup function in my workbook sucessfully but want to make
the code slightly more streamlined. The code is as follows:

=LOOKUP(C$1,Main!B2:B1000,Main!K2:K1000)

The idea is to lookup the value in cell C1 in column B on another sheet and
load the corresponding value from column K. The problem is that it only
looks in the first 1000 rows and not the whole column. I probably won't need
more than a 100 entries, or I could change it to a higher value, but I was
wondering if it could just search the whole column or is that not possible?

Many thanks

Andy Baldwin

You need to amend your formula slightly:

=LOOKUP(C1,Main!B2:K1000,10,0)

You can make the "1000" after the K as large as 65536 if you want to
look at the whole column.

Hope this helps.

Pete

Sorry Andy, I thought this was VLOOKUP - please ignore.

Pete

=LOOKUP(C$1,Main!B:B,Main!K:K)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Boggis2000" <Boggis2000@discussions.microsoft.com> wrote in message
news:CBE7554F-957B-45E4-A78C-03BEFE1E9C90@microsoft.com...
> Hello all,
>
> I am using the Lookup function in my workbook sucessfully but want to make
> the code slightly more streamlined. The code is as follows:
>
> =LOOKUP(C$1,Main!B2:B1000,Main!K2:K1000)
>
> The idea is to lookup the value in cell C1 in column B on another sheet
and
> load the corresponding value from column K. The problem is that it only
> looks in the first 1000 rows and not the whole column. I probably won't
need
> more than a 100 entries, or I could change it to a higher value, but I was
> wondering if it could just search the whole column or is that not
possible?
>
> Many thanks
>
> Andy Baldwin

Thanks, I knew it would be simple!

"Bob Phillips" wrote:

> =LOOKUP(C$1,Main!B:B,Main!K:K)
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Boggis2000" <Boggis2000@discussions.microsoft.com> wrote in message
> news:CBE7554F-957B-45E4-A78C-03BEFE1E9C90@microsoft.com...
> > Hello all,
> >
> > I am using the Lookup function in my workbook sucessfully but want to make
> > the code slightly more streamlined. The code is as follows:
> >
> > =LOOKUP(C$1,Main!B2:B1000,Main!K2:K1000)
> >
> > The idea is to lookup the value in cell C1 in column B on another sheet
> and
> > load the corresponding value from column K. The problem is that it only
> > looks in the first 1000 rows and not the whole column. I probably won't
> need
> > more than a 100 entries, or I could change it to a higher value, but I was
> > wondering if it could just search the whole column or is that not
> possible?
> >
> > Many thanks
> >
> > Andy Baldwin
>
>
>