VLOOKUP with Multiple Criteria in Multiple Worksheets

Hey Guys, you helped me before but I had to change the way I pulled the data and tried copying exactly what was done before to solve my issue but to no avail. There is some very small changes but I tried manipulating your formula and could not get it to work. Please see attached new sheet. Your old formula for Main N2 was: =(VLOOKUP(VLOOKUP(SUBSTITUTE($E2,"@",""),Teams,2,FALSE),INDIRECT(IF($C2="C", "Center",$C2)&"!A3:O32"),15,FALSE)-INDIRECT(IF($C2="C", "Center", $C2)&"!B35"))/INDIRECT(IF($C2="C", "Center", $C2)&"!B35")

Trying to do the following in the attached spreadsheet: Return a value when 1 condition is met via Look-up:

To determine a value in Main O2 I need:

Main B2 to determine the TAB (ex: PG, SG, SF, PF, C)
Once TAB is determined Main D2 determines team to find (ex: @MIN, @PHO, MIL, DEN, etc...however teams in Main D2 position are abbreviated and some have an "@" in front of them)
Once TAB and Team determined the result in O2 should populate from:

TAB (PF)
TEAM (@MIN)
Column C17 (44.8)

So Main O2 should equal 44.8

-----------------------------------------

Same logic applies to Main P2:

Main B2 to determine the TAB (ex: PG, SG, SF, PF, C)
Once TAB is determined Main D2 determines team to find (ex: @MIN, @PHO, MIL, DEN, etc...however teams in Main D2 position are abbreviated and some have an "@" in front of them)
Once TAB and Team determined the result in P2 should populate from:

TAB (PF)
TEAM (@MIN)
Column D17 (54.3)

So Main P2 should equal 54.3

Lastly is the Main N2 column:

This is an equation that should equal the average of ((Main O2 + Main P2)- League Average)/League Average. League Average is based on the tab and can be found in that TABs B35 position.

Ex: Main N2 would equal (49.55-46.575)/46.575 and expressed as a percent. Answer being 6.39% where:
49.55 is the average of Main O2 and Main P2
46.75 comes from the correct tab (PF in this ex) and found in the B35 position.

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )