Forum Discussion
lookup value from table
Hi,
I need to do a Lookup value in PBI (like Vlookup in excel) but using two column or Table array and in case has an error type "Error" or anything I want. I think using also OR / IF / IFERROR but i couldn't find a logical combination.
See Example below :
| Table 1 | Table 2 | Data Result |
| A1 | A2 | 1 |
| B1 | B2 | 2 |
| C1 | C2 | 3 |
| Table | Data result | |
| A1 | 1 | |
| B1 | 2 | |
| C1 | 3 | |
| A2 | 1 | |
| B2 | 2 | |
| C2 | 3 | |
| A3 | #N/A | |
| B3 | #N/A | |
| X | #N/A |
Hi cristianml,
You can create a calculated column like this: (I suppose the above table is named as 'Table6', the bottom one is named as 'Table7')
Result = IF ( LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ) <> BLANK (), LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ), LOOKUPVALUE ( Table6[Data Result], Table6[Column2], Table7[Table] ) )Alternatively, you can unpivot 'Table6' to convert its table structure to below:
Then, to do a Lookup value, please use this formula:
Result = LOOKUPVALUE ( 'Table6'[Data Result], 'Table6'[Value], Table7[Table] )
Best regards,
Yuliana Gu
3 Replies
Why not convert the two columns into one? Make a copy of the table, keep the first column in the first table and the second column in the second, give them the same name then append it?
Hi cristianml,
You can create a calculated column like this: (I suppose the above table is named as 'Table6', the bottom one is named as 'Table7')
Result = IF ( LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ) <> BLANK (), LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ), LOOKUPVALUE ( Table6[Data Result], Table6[Column2], Table7[Table] ) )Alternatively, you can unpivot 'Table6' to convert its table structure to below:
Then, to do a Lookup value, please use this formula:
Result = LOOKUPVALUE ( 'Table6'[Data Result], 'Table6'[Value], Table7[Table] )
Best regards,
Yuliana Gu
Thank you, ... Unpivot columns was the most easy way. Thanks !
