VLOOKUP based on multiple criteria

[BMil8]

Hello again!,

I'm trying to create an Excel based dashboard for my company. One part of the dashboard is a Top 20 Accounts based on territory, region, or district. Each of these three areas has source data in a seperate table. I need the top 20 vlookup to pull from the territory table if I enter a territory name in cell B20, pull from the region table if I enter a region name, and so forth. There are 10 different regions, 2 different districts, and 51 different territories.

I'm thinking that the answer somehow involves an IF statement. e.g. If cell B20 equals a region name, then perform the vlookup on the region table. If it contains a district name, pull from the district table. Otherwise pull from the territory table. I'm also thinking I may want to list all of the territories, regions, and districts in their own seperate column so the formula can say something like "If cell B20 is found in column M, pull from the territory table, if it's in column N, pull from the region table. Is this possible?

I realize this post may only make sense in my head so please let me know what other info I should provide.

With great appreciation,
Brian

 

[BMil8]

Well I seemed to have figured it out. Not sure if there is a more condensed formula...

=IF(COUNTIF(Sheet1!$AQ$1:$AQ$11,Sheet1!$G$149)>0,VLOOKUP(Dashboard!$J30,Sheet1!$A$325:$K$345,7,FALSE),IF(COUNTIF(Sheet1!$AR$1:$AR$11,Sheet1!$G$149)>0,VLOOKUP($J30,Sheet1!$A$325:$P$345,12,FALSE),IF(Sheet1!$G$149="Nation",VLOOKUP(Dashboard!$J30,Sheet1!$A$325:$U$345,17,FALSE),VLOOKUP(Dashboard!$J30,Sheet1!$A$325:$F$345,2,FALSE))))

Cell B20 is now cell G149.

 

[Aladin Akyurek]

Is this Top 20 list based on some performance score?

 

[BMil8]

Yes, it is based on # of new sales.