Lookup function etc.

[Elijah]

Hi, I'm trying to use the Lookup function to keep track of change in prices
for particular products and apply quantity to determine revenue.

For example I use the following row lookup:
=LOOKUP(A1,MonthRange,PriceChanges)
Range: A1 to L1 is the lookup month;
MonthRange: Refers to the month where prices have change. This is located in
another sheet in range A1:F1
PriceChanges: Refers to the actual prices of Product A located in row A2:F2.

The lookup function works as expected but I have quite a few products and
want to avoid creating a Named range for each product.

My question: Is there a way to lookup a range (depending on product type) to
use in the last part of LOOKUP function, as per my example?

I hope this is not to confusing..

Does anyone have any suggestions?

Elijah

 

[Frank Kabel]

Hi
If you have your product information stored in the rows starting in row
2 (A2:F2) try the following:
- on this lookup sheet use column A for storing the product name. e.g.
in A2 insert 'Product A')
- now use the following formula
=INDEX('data'!$A$1:$G$100,MATCH("Product
A",'data'!$A$1:$A$100,0),MATCH(A1,'data'!$A$1:$G$1,0))

 

[Elijah]

Yes - that's exactly what I needed Frank. Thanks.

The only modification made was to use 1 instead of 0 in the last MATCH
function since I want to pick up the last price if no change occurred.

Elijah