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 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
