It seems like such an easy thing: As I would do in excel, I just want to use a cell in my row to determine which column to return value from.
An example of my (VERY simplified) table "tTable" is:
<tbody>
</tbody>
In excel I would write this in F2:
=VLOOKUP([@Part], tTable, MATCH([@Vendor],tTable[#Headers],0), FALSE) = $1.00
OR
=ADDRESS(ROW(),COLUMN(INDIRECT("tTable["&tTable[@Vendor]&"]")))=$1.00
However, I'm trying to do this same thing in PowerPivot. My data is essentially the same. What I WANT to do is:
=LOOKUPVALUE(INDIRECT("tTable["&[Vendor]&"]"), [Part], [Part])
Which obviously doesn't work. I am out of ideas and cannot find a solution online. Excel does not work since the data set is far too large, and powerpivot is my only alternative.
Is there a formula for looking up a dynamic column in PowerPivot?
An example of my (VERY simplified) table "tTable" is:
Part | Default Vendor | Supplier1 | Supplier2 | Supplier3 | Price @ Default Supplier |
Material1 | Supplier1 | 1.00 | 1.25 | 1.30 | |
Material2 | Supplier2 | 4.10 | 4.00 | 4.15 | |
Material3 | Supplier3 | 3.70 | 3.40 | 3.00 |
<tbody>
</tbody>
In excel I would write this in F2:
=VLOOKUP([@Part], tTable, MATCH([@Vendor],tTable[#Headers],0), FALSE) = $1.00
OR
=ADDRESS(ROW(),COLUMN(INDIRECT("tTable["&tTable[@Vendor]&"]")))=$1.00
However, I'm trying to do this same thing in PowerPivot. My data is essentially the same. What I WANT to do is:
=LOOKUPVALUE(INDIRECT("tTable["&[Vendor]&"]"), [Part], [Part])
Which obviously doesn't work. I am out of ideas and cannot find a solution online. Excel does not work since the data set is far too large, and powerpivot is my only alternative.
Is there a formula for looking up a dynamic column in PowerPivot?