Forum Discussion

frankhofmans's avatar
frankhofmans
Helper IV
3 years ago

Indirect lookupvalue different sheets

Hello PBI experts,

 

i have a working file:

 

SalaryTableColumn
532Table 1Column 3
157Table 3Column 7
589Table 13Column 4
195Table 7Column 5
195Table 3Column 4
430Table 4Column 1
Etc  

 

And i have 20 other sheets with the different tax tables (al 20 sheets have the same layout and column structure, but different taxes based on country, age, salary etc.):

 

Table 1:

 

Column 1Column 2Column 3Column 4Column 5Column 6Column 7
100,310,311,511,712,113,514,1
201,520,122,522,823,525,127,1
307,6Etc     
415,8      
520,9 52,9    
750,1      
950,1      

 

Table 2: etc

 

In the first row of the main table i want to search for the value in table 1 and column 3 (52,9, because 520,9 is het first earlier row that i look for). In the second row and want the value in table 3 column 7. 

 

In excel, i can just create a vlookup formule with a "indirect" function and the column number. In powerBi i can only create this with 120 (20 sheets and 6 columns from 2 -7) if formulas: if(and(table = table 1, column = column 3), lookupvalue(table 1[column3],table 1[column1],main table[salary]), etc, etc, etc.

 

Does anyone have a more simple formule to solve this?

 

Many thanks in advance,

 

Regards,

 

Frank

1 Reply