Help with finding value

[ruok]

Hi

I 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.

I need to be able to locate the price of a product on any particular
day. So for example, the spreadsheet will look something like:

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

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

 

[Scott Zane]

(e-mail address removed) wrote in 35g2000cwc.googlegroups.com:

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.

I need to be able to locate the price of a product on any particular
day. So for example, the spreadsheet will look something like:

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

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.

This one would be fairly simple if you arranged your table in a different
manner. If you organize your table in the following way, it should work
just the way you want it to using the VLOOKUP formula:

NOTE: While trying this out, I personally setup Row 1 with merged cells so
that A1 and B1 are merged, and so on horizontally. Also, due to space
limitations here, I'll use only the first 6 columns (A thru F)

A B C D E F
1 Apples Pears Oranges
2 1/1/06 1.50 10/1/06 1.00 21/1/06 1.25
3 1/3/06 2.00 8/1/06 1.20 1/3/06 1.50

I then assigned a name to each group ("Apples" assigned to A1:B3, "Pears"
to C13, and "Oranges" to E1:F3).

In Cell A10, I input the date which I am looking for data for.

In Cell A12, I input the following formula:
=VLOOKUP(A10,Apples,2,0)

A10 = the date which you are looking to match something up to
Apples = The name of the group you are expecting the data to pertain to
2 = 2nd column in the selected range ("Apples")
0 = Exact Match expected