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]

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]

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]

"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