Concat a parameter into a vlookup?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephen - 28 Nov 2007 17:27 GMT

Is is possible to say

=IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))

?

Reply to this Message

Niek Otten - 28 Nov 2007 17:41 GMT

Yes.

Look in HELP for the INDIRECT() function

Post again in this same thread if you can't get it done

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

Reply to this Message

sebastienm - 28 Nov 2007 18:51 GMT

Almost. If the Weights.xls book is open, you can use the INDIRECT function:
syntax: INDIRECT(address_string)
with <address_String>: [book_name]sheet_name!range_address_or_name
So something like
ISNA(VLOOKUP($X996
, INDIRECT("[Weights.xls]worksheet_name!w" & C5 & "s")
,3,FALSE) )

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> Is is possible to say
>
> =IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))
>
> ?

Reply to this Message

Stephen - 28 Nov 2007 19:12 GMT

I see.

So even if I qualify the path to the Weights book, it simply won't work.
Damn.

Oh well.

Thanks.

> Almost. If the Weights.xls book is open, you can use the INDIRECT function:
> syntax: INDIRECT(address_string)
[quoted text clipped - 9 lines]
> >
> > ?

Reply to this Message

Niek Otten - 28 Nov 2007 19:39 GMT

<it simply won't work>

What does that mean? Error message? What message?
Wrong result? If so, supply the input values, the result you expected and what you got instead.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I see.
|
[quoted text clipped - 18 lines]
| > >
| > > ?

Reply to this Message

Stephen - 28 Nov 2007 19:51 GMT

It returns a #REF error because my weights.xls will not be open all the time.
the kicker for this to work is to have that weights workbook always open
which is not feasible in my situation.

> <it simply won't work>
>
[quoted text clipped - 23 lines]
> | > >
> | > > ?

Reply to this Message