Nested Vlookup

[vpranitha]

Hello,

Currently, have a table as below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Symbol [/TD]
[TD]Value [/TD]
[TD]Group [/TD]
[/TR]
[TR]
[TD]ABCD [/TD]
[TD]95[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]CGRD[/TD]
[TD]80[/TD]
[TD]Oranges[/TD]
[/TR]
</tbody>[/TABLE]

Cell B2 has a formula = vlookup(a2,'\\fs\gd\[PVO Q4 2018.xlsx]Main Sheet'!$A:$Z,18,0). The A2 value can be a part of a different file link, dependent on C2. For example, the file link for C2 could be \\fs\gd\[PVO Q4 2018.xlsx]Main Sheet'!$A:$Z,18,0), and the file link for C3 could be '\\fs\gd\[MNO Q4 2018.xlsx]Value Sheet'!$A:$Z,16,0). We have multiple rows up to 150 rows. Currently, we manually update for formulas for each group with respective file link by filtering for specific groups and updating the formulas respectively. Looking for a more efficient process, where if the unique group values have distinct file link and if that gets updated MOM, we only need to update the file link for the unique group value.

Thank you and appreciate the assistance.

 

[etaf]

so in your example
group APPLES = \\fs\gd\[PVO Q4 2018.xlsx]Main Sheet'!$A:$Z,18,0),
group ORANGE = '\\fs\gd\[MNO Q4 2018.xlsx]Value Sheet'!$A:$Z,16,0)

and other groups would have different link IDs

you could use an INDIRECT() to the links
so a list of the groups and links

then in the formula it would use am INDIRECT() and lookup what link to use

am i correct in what you would like to do

 

[vpranitha]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Symbol [/TD]
[TD]Factor[/TD]
[TD]Price [/TD]
[TD]Group [/TD]
[/TR]
[TR]
[TD]VNHY[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]NHYG[/TD]
[TD]0.5[/TD]
[TD]99.5[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]NHJU[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]hyuh[/TD]
[TD]0.6[/TD]
[TD]98.5[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]nhju[/TD]
[TD]0.5[/TD]
[TD]100[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]njuh[/TD]
[TD]0.6[/TD]
[TD]88[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]kjuy[/TD]
[TD]0.7[/TD]
[TD]65[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]mjuh[/TD]
[TD]0.5[/TD]
[TD]45[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]NJK[/TD]
[TD]0.6[/TD]
[TD]99[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]NJHU[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]Apples[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for taking time to answer the question. To further elaborate , the values for Factor and Price column is in different workbooks, with unique reference columns ( not the same for all groups). Currently we filter the group and paste vlookup formula of symbol for just the groups.

Eg

B2=vlookup(a2,'[NMO Q4 2018_result.xlsx]Main Sheet'!$A:$Z,18,0)
C2=vlookup(a2,'[NMO Q4 2018_result.xlsx]Main Sheet'!$A:$Z,20,0)
B1=vlookup(a1,'[onj Q4 2018_result.xlsx]Main Sheet'!$A:$Z,18,0)
C1=vlookup(a1,'[onj Q4 2018_result.xlsx]Main Sheet'!$A:$Z,20,0)

Trying to make this manual process more automatic, where instead of filter by group and pasting vlookup formula, if the link for group is updated, automatically updates the sheet. Tried and if(d2="Apples",vlookup(a2,indirect(f2),0),"N/A"), where f2= '[NMO Q4 2018_result.xlsx]Main Sheet'!$A:$Z,18. Gives me a ref error, tried having the NMO sheet open, still get the same error. Maybe, am complicating the syntax.

Hope this helps in explaining my issues more clearly. Thanks again for the assistance.

 

[etaf]

just to clarify

APPLES GROUP
B2=vlookup(a2,'[NMO Q4 2018_result.xlsx]Main Sheet'!$A:$Z,18,0)
C2=vlookup(a2,'[NMO Q4 2018_result.xlsx]Main Sheet'!$A:$Z,20,0)

BANANAS GROUP
B1=vlookup(a1,'[onj Q4 2018_result.xlsx]Main Sheet'!$A:$Z,18,0)
C1=vlookup(a1,'[onj Q4 2018_result.xlsx]Main Sheet'!$A:$Z,20,0)

PEACH GROUP
B1=vlookup(a1,'[some other name '!$A:$Z,18,0)
C1=vlookup(a1,'some other name'!$A:$Z,20,0)

is that correct ?

 

[vpranitha]

Yes .

 

[etaf]

OK,
so i have played with different sheets to start with

=VLOOKUP(A2,INDIRECT(VLOOKUP(D2,$G$2:$H$3,2,FALSE)),2,FALSE)

where INDIRECT(VLOOKUP(D2,$G$2:$H$3,2,FALSE))
lookups the sheet and range for a list on the sheet

and this works - SO it should work with an external sheet when OPEN at the same time

so three workbooks to test , as its very complicated to describe

1) Groupsheet_etaf - which has all the groups etc
https://www.dropbox.com/s/nls9pa5ehzw88hf/GroupSheet_etaf.xlsx?dl=0

2) Apples - sheet where the apple is the reference sheet for info for the apples group
https://www.dropbox.com/s/4393rsxfbot27r1/Apples.xlsx?dl=0

3) bananas - sheet for where the bananas group info is
https://www.dropbox.com/s/w1vy1koy53gsb75/Bananas.xlsx?dl=0


so save all three
open all three

now on the groupsheet - you will see a range g2:h3 - this is where you list all the ranges for each group
but it has to start with two ' looks like a " inverted comma - but its NOT

 

[vpranitha]

Thank you very much this worked perfect. Included another vlookup for the column number 2, in the formula =VLOOKUP(A2,INDIRECT(VLOOKUP(D2,$G$2:$H$3,2,FALSE)),2,FALSE), since they can be dynamic as well.

As a follow up, how can we make this work with closed reference sheets. For the purpose of this exercise serves the goal, just wanting to learn if it is possible.

Thanks again.

 

[etaf]

I tried to make it dynamic
yes, closed books I used indirect.ext - BUT not since about 2008 and on a windows machine
I only have available at the moment OSX on apple - so not sure if it still works

heres some info on the function along with alternatives - again NOT tried
http://www.ashishmathur.com/tag/indirect-ext/
https://www.mrexcel.com/forum/excel-questions/86381-indirect-ext-morefunc-xll-addin.html