Multi-level vlookup

[Peter Haworth]

Hope someone can help me with this.

I have a table I need to access with VLOOKUP (I think). Each row has a
product code (the column used to do the lookup), an effective date, and a
price. So if product A cost $10 starting from 1/1/2003 and $11 starting
6/1/2003, the entries would be:

Product A 1/1/2003 $10.00
Product B 6/1/2003 $11.00

When data is being entered into the spreadsheet, the product code and date
of sale are entered by the user. How can I access the table to get the
correct price? Or maybe I need a different table structure, or even a
different methodology?

I have tried having a two-column table (omitting the date), and just
changing the price when appropriate, but as soon as I change the price, the
spreadsheet columns that calculate price * quantity chenge to reflect the
new price.

Thanks,
Pete

 

[Karen]

pete:

if i understand correctly, you're saying that someone
could enter the product into column E, the start date into
column F, and then you'd want an equation in column G to
retrieve the corresponding price? if that's what you're
looking to do, and your data is set up in columns A, B,
and C (as you suggested below), the equation you'd enter
into column G would look as follows (assuming you had 1000
lines of data and no header row):

{=INDEX(C$1:C$1000,MATCH(E1&F1,A$1:A$1000&B$1:B$1000,0))}

the brackets {} indicate an array-entered equation. don't
actually type them in, but rather than hitting enter to
enter your equation, type control-shift-enter.

you can enter different values into columns E & F, and
then fill the equation down in column G and you should
return the correct corresponding prices for the pairs from
your data.

hope that helps,
karen

 

[Peter Haworth]

Karen,
Thanks, I think I understand how that works but couple of follow up
questions.

Given that there are dates involved in this, does the "&" concatenation
function still work OK?

Also, I think I would need to use something other than zero as the final
argument for the match function. The dates in the lookup table would be
start dates in essence, whereas the date entered by the user could be any
date. So I would need MATCH to find the entry with the highest date value
less than the date entered by the user so I think the match-type argument
should be 1?

Thanks for the guidance.

Pete

 

[Peter Haworth]

Karen,
Please ignore my earlier reply to your post - I just tried your forula and
everything works great afet I change the match type to 1.

Here's my latest problem. The calculation I need to do are more complicated
than in my original question. The cell that needs to include the array
formula actually uses the CHOOSE function to perform one of several actions
depending on other data conditions, but only the solution you gave me
involves using an array formula. Ater editing the formula for the cell to
include your array formula, I press ctrl-shift-enter and nothing happens,
that is the "{" and "}" are not inserted into the formula.

Is it not possible to include a mix of array and non-array formulas in the
same cell?

Thanks,
Pete

 

[Karen]

you should always be able to array-enter an equation and
the brackets {} should appear - i have absolutely no idea
why you would do ctrl-shift-enter and nothing happen - are
you still holding the control and shift keys down when you
hit enter?

and yes - you can mix array- and non-array-entered
equations into one - as long as you ahve the array-enter
there for those conditions where the equation requires it.

hope that helps!
- karen

 

[Peter Haworth]

Karen,
Thanks, the array enter is working now. I'm not sure what I did wrong, but
no matter.
Pete