JosephK wrote:
>
> On 05:00 Sun 20 May , TerryJ wrote:
>>
>>
>>
>> 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?
>> >>
>> > 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
>> >
>>
>> 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.
>>
> Got It. Thanks Terry. Used MATCH instead of HLOOKUP
> =VLOOKUP(D2;Prices.$A$1:$D$4;MATCH(C2;Prices.$A$1:$A$4;1))
> works fine
>
> Regards
> --
> It is often safer to be in chains than to be free.
>
> Joseph K
>
>
MATCH is the ideal solution.
--
View this message in context:
http://www.nabble.com/LOOKUP-reference-problem-tf3785120.html#a10705965
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
