On 11:17 Sun 20 May , Brian Barker wrote:
> 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?
>
> 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 tried building the formula bit by bit. All the separate parts work
individually but the offset part of the LOOKUP does not seem to accept a
formula as a reference; it needs a string or integer corresponding to
the column header or number. First formula attempt was:
=VLOOKUP(D2;Prices.$A$1:$D$4;HLOOKUP(C2;Prices.$A$1:$D$4;1))
in cell F2
Customer table:-
A B C D E F
1 AccountName CustomerName PriceList Item Price
2 Account1 Apex Trading PriceList1 Item1 Err:502
3 Account2 Beta Company PriceList2 Item2 Err:502
4 Account3 Crash Computers PriceList1
5 Account4 Other Company PriceList3
Prices table:-
A B C D
1 PriceList1 PriceList2 PriceList3
2 Item1 10 21 19
3 Item2 15 23 25
4 Item3 18 27 34
Also tried wrapping the HLOOKUP in a COL() to get the column number but
the ref bit is always read as a formula and not a number or string
The error I get is Err:502 which is I believe More Than One Result
Regards
--
It is often safer to be in chains than to be free.
Joseph K
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
