At 10:05 20/05/2007 +0100, 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
A simple test shows that the answer is yes: there is no reason why
the third "index" parameter of VLOOKUP or HLOOKUP cannot be an
expression, so it can certainly be a cell reference. If the relevant
cell in the record for the customer contains the appropriate column
number in the price list, what you describe ought to work.
If, as you say, this doesn't work for you, I suppose the most likely
problem is that you have the syntax of the function reference
slightly wrong. Can you perhaps check this? Is it something as
simple as a comma in place of a semicolon as parameter
separator? Can you construct a miniature version with only a few
entries on a single sheet and get that to work first - and then
extend it to the real case? Failing that, you may like to write back
to the list with details of the actual result: do you get the wrong
answer or no answer or an error? Can you copy the function reference
into your message? (Don't send any actual customer information, of course.)
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
