I'm sorry but I have no clue as to what you're talking in reference to. This is all Greek to me. This doesn't appear to apply to my problem.
Ken ----- Original Message ----- From: Brian Barker<mailto:[EMAIL PROTECTED]> To: [email protected]<mailto:[email protected]> Cc: Joseph K<mailto:[EMAIL PROTECTED]> Sent: Sunday, May 20, 2007 6:24 AM Subject: Re: [users] LOOKUP reference problem At 12:43 20/05/2007 +0100, JosephK wrote: >On 11:17 Sun 20 May , Brian Barker wrote: > > At 10:05 20/05/2007 +0100, Joseph K 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 Error 502 is "invalid argument", in fact, which makes sense. The problem here is that your HLOOKUP doesn't really do anything as it stands. Since you have asked it to return the value in row 1, what you get back when you search the column headers of the Prices table for "PriceList1" is one of the values in row 1 - in this case simply the text "PriceList1". All you have done is to confirm that this text does occur in a column header. What you would need it to return here in order for the VLOOKUP to work properly is the column number - in this case 2. And error 502 occurs because the relevant parameter needs to be a number, not a piece of text. How to solve this? One way would be to add an extra row to the Prices table with the column numbers. You would have to label column B as 2 and so on. Then if you picked that row instead of the column headers, it would work as an index in the VLOOKUP. Another way would be to use the MATCH function: this will return the index of the cell (in this case, column) in which the relevant price list name was found instead of its name. Use this in place of your HLOOKUP. This is what I'd do, I think. Do you need to put "PriceList" in so many times? Could you not have column D in the Customer table just containing the numbers: 1, 2, 1, 3, and so on? You could then use this (give or take "+1" here and there) as the index to the VLOOKUP directly. But you may prefer actual names, of course. By the way, I think you may need to put a "FALSE" final parameter in your HLOOKUP and VLOOKUP unless you are very sure about what Calc considers as sorted arrays. I trust this helps. Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]> For additional commands, e-mail: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
