Use Vlookup with Formula Cells.

[sadnblue]

Greetings,
Basic Question. How to ref a cell in vlookup (Vlookup Value) and get results.

E.g. I've Formula to get my Sheet name in A1,

Using this formula to get sheet name >> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Now I want Vlookup to lookup A1 & then carry on with the process but my formula fails as A1 is not a binary Value I guess?
how to treat A1 as a Number or Text?

 

[Norie]

Using a value that's the result of a formula should work exactly as a value that has been entered manually.

Can you post the formula and some sample data?

 

[sadnblue]

The formula in Sheet "3" A1

=MID(CELL("filename",A1),FIND("]",CELL("FILENAME",A1))+1,255)

which results in Value "3" which is sheet name.

The Formula in Sheet"3" A2

=VLOOKUP(A1,EDETAILS,2)

which should result in a value of the brand name. but instead, it Gives "N/A Error".
"EDETAILS" is the Table name from which I'm looking up the value.
Vlookup function is correct as when I change the value in A1 & Manually type Number 3 it gives the desired result.

 

[sadnblue]

Sorry, The Vlookup formula is in B1.

 

[Norie]

There are at least 2 reasons the VLOOKUP might have failed:

1. The value returned by the formula, "3", is a string, and you've stored the sheet names as numbers in the table EDETAILS.
2. You are missing the 4th argument of VLOOKUP.

 

[sadnblue]

Ok Here's what I'm trying to achieve.

I've multiple sheets with different numbers and each number represents an Employee code with his Personal details.
All employee's details are also saved on different sheets in EDETAILS table.

What I want is whenever I make a new sheet and rename that sheet with employee code the vlookup should automatically pick details of that employee from that number without me manually changing the value in A1, As A1 automatically picks a name from sheet name.

but B1 Vlookup just gives N/A Error.

 

[Norie]

Did you check if either of the reasons I mentioned in post #5 apply?

 

[sadnblue]

Unfortunately, as much as I understood it didn't.
What'd you mean by the 4th Argument? Have tried TRUE/FALSE.

 

[Norie]

The 4th argument determines whether or not to find an exact match, you would want to find an exact match so would use TRUE.

Have you checked the first reason?

The formula you posted returns a string, if you try and look that up against numeric values a match won't be found.

To check if you have numeric or text values in the sheet column of EDETAILS you can use the ISTEXT function.

 

[sadnblue]

I've Tried ISTEXT & in EDETAILS Table it returns "FALSE" But When I use that on A1 in my Lookup sheet it Returns "TRUE".