I'm working on a formula to pull values from various different tables based on the values in a drop down menu. I have been doing this with a series of nested if/then statements, but as I am adding more options to the dropdown it is getting increasingly unwieldy. My current formula that does everything i want (but is hard to expand upon) is:
=IF(IFERROR(IF(L$1=Q$2,INDEX(PNAll[Calls],MATCH(Table!D2,PNAll[Index],0)),IF(L$1=Q$3,INDEX(PNASL[Calls],MATCH(Table!D2,PNASL[Index],0)),IF(L$1=Q$4,INDEX(Orig_100[Calls],MATCH(Table!D2,Orig_100[Index],0)),IF(L$1=Q$5,INDEX(POAll[Calls],MATCH(Table!D2,POAll[Index],0)))))),0)>O$1,O$2,IFERROR(IF(L$1=Q$2,INDEX(PNAll[Calls],MATCH(Table!D2,PNAll[Index],0)),IF(L$1=Q$3,INDEX(PNASL[Calls],MATCH(Table!D2,PNASL[Index],0)),IF(L$1=Q$4,INDEX(Orig_100[Calls],MATCH(Table!D2,Orig_100[Index],0)),IF(L$1=Q$5,INDEX(POAll[Calls],MATCH(Table!D2,POAll[Index],0)))))),0))
For my new (easier to update) version I have created:
=INDEX(INDEX(Filter[TableRef],MATCH($L$1,Filter[Source Filter],0))&"[Calls]",MATCH([@Index],INDEX(Filter[TableRef],MATCH($L$1,Filter[Source Filter],0))&"[Index]",0))
The idea being instead of having to add each new table as a separate if/then I can just add it as a new line to the TableRef table and it will point there automatically. I am almost certain the issue is coming where I am trying to concatenate the table column reference into the table name, but I'm not sure how to work around the issue.
Thanks!
=IF(IFERROR(IF(L$1=Q$2,INDEX(PNAll[Calls],MATCH(Table!D2,PNAll[Index],0)),IF(L$1=Q$3,INDEX(PNASL[Calls],MATCH(Table!D2,PNASL[Index],0)),IF(L$1=Q$4,INDEX(Orig_100[Calls],MATCH(Table!D2,Orig_100[Index],0)),IF(L$1=Q$5,INDEX(POAll[Calls],MATCH(Table!D2,POAll[Index],0)))))),0)>O$1,O$2,IFERROR(IF(L$1=Q$2,INDEX(PNAll[Calls],MATCH(Table!D2,PNAll[Index],0)),IF(L$1=Q$3,INDEX(PNASL[Calls],MATCH(Table!D2,PNASL[Index],0)),IF(L$1=Q$4,INDEX(Orig_100[Calls],MATCH(Table!D2,Orig_100[Index],0)),IF(L$1=Q$5,INDEX(POAll[Calls],MATCH(Table!D2,POAll[Index],0)))))),0))
For my new (easier to update) version I have created:
=INDEX(INDEX(Filter[TableRef],MATCH($L$1,Filter[Source Filter],0))&"[Calls]",MATCH([@Index],INDEX(Filter[TableRef],MATCH($L$1,Filter[Source Filter],0))&"[Index]",0))
The idea being instead of having to add each new table as a separate if/then I can just add it as a new line to the TableRef table and it will point there automatically. I am almost certain the issue is coming where I am trying to concatenate the table column reference into the table name, but I'm not sure how to work around the issue.
Thanks!