Nesting Index and Match Function

[dubez]

Hi friends,

I have a formula that works, however I am not able to nest this formula to get more conditions. This could be very easy or a bit tricky.
I have the following formula that looks up all the records for an ID belonging to a particular territory:

=(INDEX('[IA Territory Dashboard WORKSHEET.xlsx]Ontario'!$A$3:$A$11,SMALL(IF(Dashboard!$B$7='[IA Territory Dashboard WORKSHEET.xlsx]Ontario'!$H$3:$H$11,ROW('[IA Territory Dashboard WORKSHEET.xlsx]Ontario'!$A$3:$A$11)-MIN(ROW('[IA Territory Dashboard WORKSHEET.xlsx]Ontario'!$A$3:$A$11))+1,""),ROW(A1))))

I would like to nest this so that I can dynamically lookup values in other territories (i.e. when "Dashboard!$B$7='[IA Territory Dashboard WORKSHEET.xlsx]Quebec" it will show data in the Quebec worksheet).

Any help is greatly appreciated.

 

[saeedkk]

Hello,

it is not very well clear, but from what i understand you need to do, you would need to use INDIRECT and concatenate functions which you would be able to build a string and then change it to a reference based on what logic you need

if you can provide me with workbook it will be easier to fix this for you, but i think you would be able to solve it on your own with the above suggestions...

Hope this helps

Regards

 

[dubez]

Thanks saeedkk, so sorry for the late reply and sorry if I wasnt that clear.

It turns out the formula I mentioned above has all the credentials to dynamically look up values in other territories (I must've been overthinking it).
The "IF(Dashboard!$B$7='[IA Territory Dashboard WORKSHEET.xlsx" part of the formula is where it essentially becomes dynamic based on the value in the Dashboard tab.

Thanks for all the help and sorry for the confusion.