Lookup Formula Equivalent in Powerpivot?

[benvans]

First-time poster and Powerpivot newbie, so please bear with me.

I am looking for an equivalent to the Excel lookup() formula in Powerpivot. I have one large set of data in Powerpivot where I calculate annual invoice volume per invoice among 300,000 rows of invoice data.

I have a separate small set of data in Powerpivot which is used to assign a "Volume Tier" based on the calculated invoice volume and assigned volume break. It groups the invoice volume data into "Tiers" based on a pre-assigned volume Break per invoice. It looks as follows:

Volume Tier___Break A___Break B___Break C
Tier 1________0________0________0
Tier 2________50000____5000_____1000
Tier 3________100000___25000____2500
Tier 4________500000___50000____5000

Pre-powerpivot, I used a nested if() and lookup() formula to assign the appropriate Volume Tier to my large set of data. For example, if Volume Break = "Break A" and Invoice Volume = 15,000, then the result would be "Tier 1". If Volume Break = "Break A" and Invoice Volume = 101,000, then the result would be "Tier 3".

I have both Volume Break and Invoice Volume in my large Powerpivot sheet, but can't figure out how to create a calculated column in Powerpivot to pull the Volume Tier in from the second sheet. Thanks!

 

[benvans]

Found the answer here:
http://social.msdn.microsoft.com/Fo...737-8306-4e26-bcbd-382414072541?prof=required