JosephK wrote:
>
> 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
>
>
The nested formula has to return an integer. In other words, the price list
reference has to be the equivalent of the offset number in the price list.
--
View this message in context:
http://www.nabble.com/LOOKUP-reference-problem-tf3785120.html#a10705257
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
