vlookup - again

[gumby]

I have this =VLOOKUP(B17,$A$40:$B$49,2,FALSE) in my sheet. cell b17 has "=a43".(I'm assuming this is the problem) It gives me the error #N/A. how can I format the b17 cell reference in vlookup so it will see it as a cell reference and not text.

 

[gumby]

they are all on the same sheet

I tried =OFFSET(INDIRECT(b17),,1) and got #REF!

 

[Peo08]

Try

=OFFSET(INDIRECT(TRIM(B17)),,1)

you must have a space or something in B17 as well as a43,
if B17 contains a cell address then the above formula will return whatever is the
first cell to the right of that cell address.. The only way it can return a ref error is if it is not a cell reference.

Regards,

Peo Sjoblom

 

[Aladin Akyurek]

On 2002-04-07 15:19, gumby wrote:
they are all on the same sheet

That's the trouble.

You should actually get #N/A and a circular reference problem.

You have =A43 in B17.

A43 is part of the range $A$40:$B$49 that is your lookup table as can be seen from your formula:

=VLOOKUP(B17,$A$40:$B$49,2,FALSE)

and you stated to have this formula in

B43

which is also a part of the range $A$40:$B$49 which is your lookup table.

It boils down to: VLOOKUP is forced to retrieve the result that it must self compute/retrieve.

What now?

It would be better if you could explain what you are trying to do.

Aladin