Cell Content as Vlookup Array Table



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!



Hi there! Do you mean a Vlookup?
don (rep: 1835) 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: 3908) 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


Selected Answer

Please try using the INDIRECT() function.


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.



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: 3908) 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: 3908) 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: 3908) Aug 1, '20 at 8:29 pm
Add to Discussion

Answer the Question

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