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