vlookup value not working

[Anonymous]

i have two table  table1 and table2  both  table common value product code i trying to vlookup the value not working 

Table 1 

Product        M1        M3      Actuals 

 FA0001        52         45         435

 FA0003        52         45         435

 FA0004        52         45         435

 FA0005        52         45         435

 FA0006        52         45         435

 FA0007        52         45         435

Table 2 :

Product     SPVCode        SPVDesc   Product Category 

 FA00045        10052                45           435

 FA0003          20052                45          435

 FA0004          30052                45          435

 FA00015         40052               45            435

 FA0006          50052                45            435

my outout want look like 

Table 1 

Product        M1        M3      Actuals  SPVCode        SPVDesc   Product Category 

 FA0001        52         45         435

 FA0003        52         45         435       20052                45              435

 FA0004        52         45         435       30052                45               435   

 FA0005        52         45         435 

 FA0006        52         45         435        50052              45                435

 FA0007        52         45         435

i am have tired with lookup value function i am getting Error 

SPDCODE = LOOKUPVALUE('table2'[Product],'table2'[SPVCODE ],MAX('Table1'[Product]))

Function 'LOOKUPVALUE' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[Jos_Woolley]

Hi,

Not quite sure why MAX and MAXX are being used here. Aren't you simply wishing to perform a straight lookup?

SPVCODE = LOOKUPVALUE('Table2'[SPVCode],'Table2'[Product],[Product])

If you create a relationship between the two tables based on the Product column, you could use simply:

SPVCODE =
RELATED ( Table2[SPVCode] )

Regards

[Greg_Deckler]

@Jos_Woolley I was assuming with the MAX that this was a measure but if it is a column you are correct. Using the MAXX(FILTER(...)...) form instead of LOOKUPVALUE solves the instance where multiple rows are returned with the same value. In this case, LOOKUPVALE will fail while MAXX will succeed.

[Jos_Woolley]

Ah, thanks, good point. It didn't even cross my mind that the OP might be wanting a measure here, not a calculated column.

Regards

[Greg_Deckler]

@Anonymous You have your result and search columns reversed:

SPDCODE = LOOKUPVALUE('table2'[SPVCODE ],'table2'[Product],MAX('Table1'[Product]))

[Anonymous]

@Greg_Deckler  i am getting error like below 

Function 'LOOKUPVALUE' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[Greg_Deckler]

@Anonymous LOOKUPVALUE has the form of:

LOOKUPVALUE(<result column>, <search column>, <search value)

Seems like your Product columns are both text, not sure how they could be numeric so they should be the second and third parameters. Your SPVCODE column looks like it is numeric. Having it be the result column should not produce that error since it is not being compared. 

You can try an alternative method:

SPDCODE = MAXX(FILTER('table2','table2'[Product] = MAX('Table 1'[Product])),[SPVCODE])