Changing LOOKUP table prices changes previous sales too!

**K m** · 08-02-2012, 01:22 PM

- You would set up a Table to indicate the correct price for a given day
- What you do is setup a lookup formula probably VLOOKUP
- Using the DATE, Excel would look up the correct PRICE.

If you need any more help, you may want to load sample file so I could help

---------- Post added at 01:22 PM ---------- Previous post was at 11:05 AM ----------

The formula would like this:

=Vlookup(A1,Price_Array, 2, False)

where: A1= lookup_date {change accordingly}

Price_Array = Array of Dates and Prices {I would define a named range}

2 = col_index, {In this case, values are the 2nd column}

False = {Dates and prices do not have to be in order}

**CaneRivero** · 08-02-2012, 03:03 PM

The formula would like this:

=Vlookup(A1,Price_Array, 2, False)

where: A1= lookup_date {change accordingly}

Price_Array = Array of Dates and Prices {I would define a named range}

2 = col_index, {In this case, values are the 2nd column}

False = {Dates and prices do not have to be in order}

Please see attached. If the cost of Cats goes up to $2.00(and in this economic climate that's a real possibility)I change B2 in ProductLookup to reflect that for future sales. However that will chance my cost for the Cat I sold on 6/12/12.

My sense of it that identifying the change by date will cause a new row to be created.

Maybe it would be simpler to us PASTE/SPECIAL/VALUES occasionally.

Thank you ever so much for this help.

Cane

**K m** · 08-02-2012, 05:01 PM

This is what I had in mind

=IF(C2="",0,INDEX(ProductLookup!$B$3:$G$11,MATCH(C2,ProductLookup!$A$3:$A$11),MATCH(B2,ProductLookup!$B$1:$G$1)))

The First MATCH uses the SKU to find the proper row in the ProductLookup
The Second MATCH looks to see at the DATE and matches the proper column in the ProductLookup. Therefore if there has been a price increase before the ship date, the new price is reflected.

I am sending you your file back with the equations

I hope I explained it well enough

**CaneRivero** · 08-04-2012, 08:07 AM

Originally Posted by K m

This is what I had in mind

=IF(C2="",0,INDEX(ProductLookup!$B$3:$G$11,MATCH(C2,ProductLookup!$A$3:$A$11),MATCH(B2,ProductLookup!$B$1:$G$1)))

The First MATCH uses the SKU to find the proper row in the ProductLookup
The Second MATCH looks to see at the DATE and matches the proper column in the ProductLookup. Therefore if there has been a price increase before the ship date, the new price is reflected.

I am sending you your file back with the equations

I hope I explained it well enough

You're too kind, K m - thank you. I really don't expect people who help me to "do it for me", but I realize it could take a lot more of your effort and time to teach me.

And, I absolutely had the wrong thing in mind. Had you helped me do what I was trying to do, you'd have helped me make a mess.

I understand this well enough to use it now, and probably to make future use of it. This is going to be a heck of a tool for me.

I just can't thank you enough...

Best regards,
Cane

---------- Post added at 07:07 AM ---------- Previous post was at 06:55 AM ----------

K m -

Cane Rivero here - again.

I'm interested in the idea of "lock in" previous prices.

As I move my cursor around the spreadsheet, sometimes unintended things happen. 1st, I have to learn how to move the cursor around without having that happen, and I will. But...

...can you suggest a sort of "best practice" ?

"Lock in" say, at the end of each week, would (I think) do it...but since I know so little maybe you could tell me if there is a more appropriate way of keeping things from changing?

Regards,
Cane