use a function to look up a result in a spreadsheet

[L Smith]

Hi

I have previously posated this on hte Google newsgroup, but had no replies
so apologies for cross-posting

! am using Excel 2000 and am based in the UK - so UK date format
applies.

I have 'inherited' a large spreadsheet with historic pricing
information. The sheet lists each product line in column A, then as
each price changes the cells in the next two blank columns are
completed, respectively, with the date of the change and the new price.
Not every product changes price on the same day.

So for example, the spreadsheet will look something like (assuming this
displyys correctly):

A B C D E F G
1 Apples 1/1/06 1.50 1/3/06 2.00
2 Pears 10/1/06 1.00 8/1/06 1.20 9/3/06 1.45
3 Oranges 21/1/06 1.25 1/3/06 1.50 1/4/06 1.45
4 Plums 1/1/06 1.50 1/4/06 1.75
5 Grapes 1/2/06 1.25 1/3/06 1.75 1/4/06 1.95


I need to be able to locate the price of a product on any particular
day. If I want to get the price of say Oranges on 14/3/2006 manually, it is
clearly the value in cell E3. But how can I get this automatically? I
want to be able to enter the product and date in separate cells, and
have Excel put the correct price in a third.


I could probably do this with a macro but want to avoid that if
possible, as I thought it must be possible to do this with an inbuilt
function. I have tried various combination of VLOOKUP, HLOOKUP, MATCH
and INDEX, all of which seem to go some way towards what I want, but I
have not been able to work out how to get this to work. I have
searched the newsgroup but haven't found anything that I can see helps.


I can't help feeling this should be fairly simple and I'm missing
something obvious! Grateful for any ideas.


LS

[Toppers]

Using the table below in A1:G6 (row 1 is header) try this:

=INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))

The offset allows for 20 columns of data so adjust as required.

Where L1=Product
L2=Date

To cater for errors:

=IF(ISERROR(INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))),"",INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0)))


Product Date Price Date Price Date Price
Apples 01/01/2006 £1.50 01/03/2006 £2.00
Pears 10/01/2006 £1.00 08/01/2006 £1.20 09/03/2006 £1.45
Oranges 21/01/2006 £1.25 01/03/2006 £1.50 01/04/2006 £1.45
Plums 01/01/2006 £1.50 01/04/2006 £1.75
Grapes 01/02/2006 £1.25 01/03/2006 £1.75 01/04/2006 £1.95

HTH

"L Smith" wrote:

> Hi
>
> I have previously posated this on hte Google newsgroup, but had no replies
> so apologies for cross-posting
>
> ! am using Excel 2000 and am based in the UK - so UK date format
> applies.
>
> I have 'inherited' a large spreadsheet with historic pricing
> information. The sheet lists each product line in column A, then as
> each price changes the cells in the next two blank columns are
> completed, respectively, with the date of the change and the new price.
> Not every product changes price on the same day.
>
> So for example, the spreadsheet will look something like (assuming this
> displyys correctly):
>
> A B C D E F G
> 1 Apples 1/1/06 1.50 1/3/06 2.00
> 2 Pears 10/1/06 1.00 8/1/06 1.20 9/3/06 1.45
> 3 Oranges 21/1/06 1.25 1/3/06 1.50 1/4/06 1.45
> 4 Plums 1/1/06 1.50 1/4/06 1.75
> 5 Grapes 1/2/06 1.25 1/3/06 1.75 1/4/06 1.95
>
>
> I need to be able to locate the price of a product on any particular
> day. If I want to get the price of say Oranges on 14/3/2006 manually, it is
> clearly the value in cell E3. But how can I get this automatically? I
> want to be able to enter the product and date in separate cells, and
> have Excel put the correct price in a third.
>
>
> I could probably do this with a macro but want to avoid that if
> possible, as I thought it must be possible to do this with an inbuilt
> function. I have tried various combination of VLOOKUP, HLOOKUP, MATCH
> and INDEX, all of which seem to go some way towards what I want, but I
> have not been able to work out how to get this to work. I have
> searched the newsgroup but haven't found anything that I can see helps.
>
>
> I can't help feeling this should be fairly simple and I'm missing
> something obvious! Grateful for any ideas.
>
>
> LS
>
>
>

[L Smith]

Thanks for the help Toppers. Unfortunately, when I tried to use this, the
cell shows # N/A which I understand is a 'number not available' error. When
I use the Evaluate Formula option to check the calculation steps, everything
seems to be going fine until it comes to evaluate the 'OFFSET' section, where
I get the following result:

INDEX ($B$2:$G$6,#N/A, MATCH(38777,FFSET($A$1,#N/A,0,1,20),0))

(38777 is the numerical representation for 1/3/2006) The second #N/A is in
italics.

Any ideas of what is happening?

Thanks

LS





"Toppers" wrote:

> Using the table below in A1:G6 (row 1 is header) try this:
>
> =INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))
>
> The offset allows for 20 columns of data so adjust as required.
>
> Where L1=Product
> L2=Date
>
> To cater for errors:
>
> =IF(ISERROR(INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))),"",INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0)))
>
>
> Product Date Price Date Price Date Price
> Apples 01/01/2006 £1.50 01/03/2006 £2.00
> Pears 10/01/2006 £1.00 08/01/2006 £1.20 09/03/2006 £1.45
> Oranges 21/01/2006 £1.25 01/03/2006 £1.50 01/04/2006 £1.45
> Plums 01/01/2006 £1.50 01/04/2006 £1.75
> Grapes 01/02/2006 £1.25 01/03/2006 £1.75 01/04/2006 £1.95
>
> HTH
>
> "L Smith" wrote:
>
> > Hi
> >
> > I have previously posated this on hte Google newsgroup, but had no replies
> > so apologies for cross-posting
> >
> > ! am using Excel 2000 and am based in the UK - so UK date format
> > applies.
> >
> > I have 'inherited' a large spreadsheet with historic pricing
> > information. The sheet lists each product line in column A, then as
> > each price changes the cells in the next two blank columns are
> > completed, respectively, with the date of the change and the new price.
> > Not every product changes price on the same day.
> >
> > So for example, the spreadsheet will look something like (assuming this
> > displyys correctly):
> >
> > A B C D E F G
> > 1 Apples 1/1/06 1.50 1/3/06 2.00
> > 2 Pears 10/1/06 1.00 8/1/06 1.20 9/3/06 1.45
> > 3 Oranges 21/1/06 1.25 1/3/06 1.50 1/4/06 1.45
> > 4 Plums 1/1/06 1.50 1/4/06 1.75
> > 5 Grapes 1/2/06 1.25 1/3/06 1.75 1/4/06 1.95
> >
> >
> > I need to be able to locate the price of a product on any particular
> > day. If I want to get the price of say Oranges on 14/3/2006 manually, it is
> > clearly the value in cell E3. But how can I get this automatically? I
> > want to be able to enter the product and date in separate cells, and
> > have Excel put the correct price in a third.
> >
> >
> > I could probably do this with a macro but want to avoid that if
> > possible, as I thought it must be possible to do this with an inbuilt
> > function. I have tried various combination of VLOOKUP, HLOOKUP, MATCH
> > and INDEX, all of which seem to go some way towards what I want, but I
> > have not been able to work out how to get this to work. I have
> > searched the newsgroup but haven't found anything that I can see helps.
> >
> >
> > I can't help feeling this should be fairly simple and I'm missing
> > something obvious! Grateful for any ideas.
> >
> >
> > LS
> >
> >
> >

[Toppers]

Both #N/As are occuring the MATCH against product and this error (often)
happens if there is (are) extra blank(s) in one of the fields being matched.
So check the data as it is a data error.

If problem persists, send me a w/book to toppers<at>johntopley.fsnet.co.uk
and I'll have look later today (after 16:00!).

HTH

"L Smith" wrote:

> Thanks for the help Toppers. Unfortunately, when I tried to use this, the
> cell shows # N/A which I understand is a 'number not available' error. When
> I use the Evaluate Formula option to check the calculation steps, everything
> seems to be going fine until it comes to evaluate the 'OFFSET' section, where
> I get the following result:
>
> INDEX ($B$2:$G$6,#N/A, MATCH(38777,FFSET($A$1,#N/A,0,1,20),0))
>
> (38777 is the numerical representation for 1/3/2006) The second #N/A is in
> italics.
>
> Any ideas of what is happening?
>
> Thanks
>
> LS
>
>
>
>
>
> "Toppers" wrote:
>
> > Using the table below in A1:G6 (row 1 is header) try this:
> >
> > =INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))
> >
> > The offset allows for 20 columns of data so adjust as required.
> >
> > Where L1=Product
> > L2=Date
> >
> > To cater for errors:
> >
> > =IF(ISERROR(INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0))),"",INDEX(B2:G6,MATCH(L1,$A$2:$A$6,0),MATCH(L2,OFFSET(A1,MATCH(L1,$A$2:$A$6,0),0,1,20),0)))
> >
> >
> > Product Date Price Date Price Date Price
> > Apples 01/01/2006 £1.50 01/03/2006 £2.00
> > Pears 10/01/2006 £1.00 08/01/2006 £1.20 09/03/2006 £1.45
> > Oranges 21/01/2006 £1.25 01/03/2006 £1.50 01/04/2006 £1.45
> > Plums 01/01/2006 £1.50 01/04/2006 £1.75
> > Grapes 01/02/2006 £1.25 01/03/2006 £1.75 01/04/2006 £1.95
> >
> > HTH
> >
> > "L Smith" wrote:
> >
> > > Hi
> > >
> > > I have previously posated this on hte Google newsgroup, but had no replies
> > > so apologies for cross-posting
> > >
> > > ! am using Excel 2000 and am based in the UK - so UK date format
> > > applies.
> > >
> > > I have 'inherited' a large spreadsheet with historic pricing
> > > information. The sheet lists each product line in column A, then as
> > > each price changes the cells in the next two blank columns are
> > > completed, respectively, with the date of the change and the new price.
> > > Not every product changes price on the same day.
> > >
> > > So for example, the spreadsheet will look something like (assuming this
> > > displyys correctly):
> > >
> > > A B C D E F G
> > > 1 Apples 1/1/06 1.50 1/3/06 2.00
> > > 2 Pears 10/1/06 1.00 8/1/06 1.20 9/3/06 1.45
> > > 3 Oranges 21/1/06 1.25 1/3/06 1.50 1/4/06 1.45
> > > 4 Plums 1/1/06 1.50 1/4/06 1.75
> > > 5 Grapes 1/2/06 1.25 1/3/06 1.75 1/4/06 1.95
> > >
> > >
> > > I need to be able to locate the price of a product on any particular
> > > day. If I want to get the price of say Oranges on 14/3/2006 manually, it is
> > > clearly the value in cell E3. But how can I get this automatically? I
> > > want to be able to enter the product and date in separate cells, and
> > > have Excel put the correct price in a third.
> > >
> > >
> > > I could probably do this with a macro but want to avoid that if
> > > possible, as I thought it must be possible to do this with an inbuilt
> > > function. I have tried various combination of VLOOKUP, HLOOKUP, MATCH
> > > and INDEX, all of which seem to go some way towards what I want, but I
> > > have not been able to work out how to get this to work. I have
> > > searched the newsgroup but haven't found anything that I can see helps.
> > >
> > >
> > > I can't help feeling this should be fairly simple and I'm missing
> > > something obvious! Grateful for any ideas.
> > >
> > >
> > > LS
> > >
> > >
> > >

[L Smith]

"Toppers"

Thanks for the offer. I still have problems and have sent a worksheet with
the sasmple data on it.

Very grateful for your assistance.

LS

"Toppers" wrote:

> Both #N/As are occuring the MATCH against product and this error (often)
> happens if there is (are) extra blank(s) in one of the fields being matched.
> So check the data as it is a data error.
>
> If problem persists, send me a w/book to toppers<at>johntopley.fsnet.co.uk
> and I'll have look later today (after 16:00!).
>
> HTH