Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Cell Content as Vlookup Array Table

0

Namaste!

There are about fifty different tables ready for vlookup purpose. 

Based on the value in F1, result is to be extracted in G1 through vlookup.

But the table for that purpose can be any one out of those fifty. It will be as per the content in A1. If there is (e.g.) 'apple' in A1, the vlookup is supposed to go through the table 'apple' and so on. 

So, I thought if the tables take a name from the values in col. A, the search would be possible.

Expecting your help as always. 

Thank you!

excel
MS Office Version: 2007
posted by: Chhabi Acharya (rep: 111) on Sun Jul 26, 2020 at 9:34 pm
Answer
Discuss

Discussion

Hi there! Do you mean a Vlookup?
don (rep: 1989) Jul 27, '20 at 4:31 am
Welcome back Chhabi. Long time no see. Please explain the purpose of your plan. I already understand how you want to do it but any possible solution must link to the reason why you want to do it, to what end. Please don't respond to my question in the discussion. Instead, just amend your question to be more elaborate and precise.
Variatus (rep: 4889) Jul 27, '20 at 8:17 pm
@don @Variatus Thanks for the response. For the time being, I'm planning of using an extra column (B) which will contain fixed content like 'one', 'two', etc. that will also be assigned as the name of the corresponding tables and taking the content in col. B as vlookup table array. Not a tidy, though. Looking forward for your help.
Chhabi Acharya (rep: 111) Jul 28, '20 at 8:06 pm
Add to Discussion

Answers

1
Selected Answer

Please try using the INDIRECT() function.

=VLOOKUP("A",INDIRECT($A$1),2,FALSE)

In this example the named range name is in A1. The lookup value "A" could be replaced with a cell reference, of course.

In the attached workbook the search criterion is in C1. The result in B4 changes whenever you change either the loopup value or the lookup range.

posted by: Variatus (rep: 4889) on Tue Jul 28, 2020 at 8:11 pm
Discuss

Discussion

My main problem is that the value/content in col. A is not fix. So, the corresponding table array for A1 can be any one out of fifty tables. That's why I planned of taking help of col. B along with the formula you've provided and thank you very much for that.
But I'm afraid this formula may not be the one the question is seeking for, i. e. assigning cell content as table name and selecting this formula as the final answer may not satisfy the people in future though it can solve the present problem. I made a little search, not a vast one, but couldn't get a better solution. Is it like this, sir?
Chhabi Acharya (rep: 111) Jul 28, '20 at 8:42 pm
I thought you will have a data validation drop-down in A1. There could be 50 tables listed there or more. The VLOOKUP will work with any one you select. Did you actually try my suggestion? I thought you modified question was very clear and my solution does exactly what you ask there.
Variatus (rep: 4889) Jul 29, '20 at 8:46 pm
Of course, your idea is fantastic and I've applied it though there is no drop down list. I wanted to mark it as the final answer but I'm not sure whether it hits the theme of the question. Please suggest me.
Chhabi Acharya (rep: 111) Jul 30, '20 at 10:22 am
If my answer solved your problem it may also help others solve similar problems and it should be marked for that reason. However, if your problem isn't resolved I would appreciate if you would keep explaining until I'm able to give you an answer that does solve your problem. Have a good day!
Variatus (rep: 4889) Jul 31, '20 at 8:33 pm
I'm not sure if I could make the problem clear. Actually I meant to inquire if the name of a range could be updated along with the content of a cell without any link to vlookup. A non-vba solution would be of great help. Thank you.
Chhabi Acharya (rep: 111) Aug 1, '20 at 12:49 am
One picture says more than a thousand words. Please look at the workbook I have now attached to my answer. Tell me what you want different from what my example can do. consider uploading your own example.
Variatus (rep: 4889) Aug 1, '20 at 8:29 pm
Extremely sorry to create trouble time and again and also not being able to clarify the question. Though my problem has been solved, I still need the method through which a range, eg. J5:M15 can be renamed automatically with the text typed in a cell in a column. Thanks for the file and it is the same method I've been following. In this file, the ranges have a fix name but I meant cell content=range name. I couldn't upload a file because there would be just a table and nothing else. 
Chhabi Acharya (rep: 111) Aug 4, '20 at 10:32 am
Not clear why you would want to change the name of a fixed range. Such needs should be addressed by the INDIRECT function. However, are you aware of the "Name Bar"? It's at the top left of your screen, to the left of the Formula Bar and normally shows the address of the selected cell. If you select J5:M15 it would show that address but you can over-write it with a name and that name would get assigned to the selected range.
If you want to quickly assign names to 50 ranges I  suggest you use the Name Manager (in the Formula bar). There you can enter the name and specify the range in one dialog box.
Variatus (rep: 4889) Aug 4, '20 at 8:41 pm
Thank you, sir!
Chhabi Acharya (rep: 111) Aug 6, '20 at 8:42 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login