lookup value from table

[cristianml]

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

[v-yulgu-msft · Accepted solution]

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

[cristianml]

Thank you, ...  Unpivot columns was the most easy way. Thanks !

[jthomson]

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?