JosephK wrote:
>
> Is it possible to use the contents of a cell as the column or row index
> reference in VLOOKUP or HLOOKUP?
>
> My scenario is I have a table of 5000 customers with contact details
> etc. Another table holds prices for about 300 different products. Each
> customer or group of customers has a different price list according to
> negotiated contracts.
>
> One field of my customers table holds the particular price list that
> customer uses and the Prices table has fields for the different price
> lists (Approx 50 different lists).
>
> I am trying to do a lookup such that I enter a product and get the price
> of that product from the relevant price list. I've tried using HLOOKUP
> within a VLOOKUP using the customers pricelist field as a column
> reference but this does not work.
>
> Any ideas would be very welcome!
>
> Regards
> --
> It is often safer to be in chains than to be free.
> Joseph K
>
>
So, your VLOOKUP finds the customer's identity in your "customers table" and
returns the information in column x which tells you which price list is
applicable? How is that information expressed?
Let's suppose it is a column number or letter in the "prices table". I
think it needs to be a number which can be used as an offset in a VLOOKUP
formula. Then you can have a VLOOKUP formula within another. The nested
formula returns the offset column number in the "prices table". So you have
something like:
=VLOOKUP(item id; prices table; VLOOKUP(customer id; customers table; price
list column; 0); 0 )
--
View this message in context:
http://www.nabble.com/LOOKUP-reference-problem-tf3785120.html#a10704343
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
