vlookup and a function field input

[Guest]

Hey!

I am using a function to determine the name of the worksheet and place it
into a cell:

=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,6)

I then want to use the output of this cell [the first one is 1001] to feed
into a vlookup:

=VLOOKUP($K$2,List!$I$2:$K$579,2,FALSE)

This works when I type 1001 into cell K2 but does not work when I use the
first function to generate 1001 in cell K2. Both are formatted as
number|general.

I've tried several things - and the only thing that works in the vlookup
function is if I physically type the sheet name [a number] into cell K2.

Help? I must have missed something obvious.

Thank you!

Patrick

 

[Peo Sjoblom]

1. That function is not correct, you would need to specify any cell
reference or it will return wrong sheet name.

2. The result is text so you need to force it to be a number if the table
has a numeric value

=--MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,6)

should suffice


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

Thank you!!

The function works without a cell reference, but I understand the why it
"should" be there.

As for producing a number that vlookup can use - your solution works
perfectly!

Thank you!

1. That function is not correct, you would need to specify any cell
reference or it will return wrong sheet name.

2. The result is text so you need to force it to be a number if the table
has a numeric value

=--MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,6)

should suffice


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

Hey!

I am using a function to determine the name of the worksheet and place it
into a cell:

=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,6)

I then want to use the output of this cell [the first one is 1001] to feed
into a vlookup:

=VLOOKUP($K$2,List!$I$2:$K$579,2,FALSE)

This works when I type 1001 into cell K2 but does not work when I use the
first function to generate 1001 in cell K2. Both are formatted as
number|general.

I've tried several things - and the only thing that works in the vlookup
function is if I physically type the sheet name [a number] into cell K2.

Help? I must have missed something obvious.

Thank you!

Patrick