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