Use Vlookup with Formula Cells.

sadnblue

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

sadnblue

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Unfortunately, as much as I understood it didn't.
What'd you mean by the 4th Argument? Have tried TRUE/FALSE.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I've Tried ISTEXT & in EDETAILS Table it returns "FALSE" But When I use that on A1 in my Lookup sheet it Returns "TRUE".
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,585
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top