Forum Discussion

AlexanderPrime's avatar
AlexanderPrime
Icon for Solution Supplier rankSolution Supplier
4 years ago
Solved

LOOKUPVALUE Column Value from a row based on another column's value

Hi all.

 

I'm trying to get a value from another row based on a value within the current row.

 

I have the following:

Tablename: Subgroup

WKYR     TY  LY   LASTWKYR
202033    1000     201933
202133    3200    202033

 

Essentially I'm trying to make the LY column in the 202133 row pull the 1000 into it based on the 202033 value in both WKYR and LASTWKYR, with a fall back to 0 if there is no value for it to look up, which in this table would result in LY in the 202033 row be 0. 

I've tried: 

LY = LOOKUPVALUE(
Subgroup[TY],
Subgroup[LASTWKYR],
Subgroup[WKYR],
0
)
 
But it doesn't appear to work? Am I missing something obvious or am I aiming in the wrong direction here? Unable to use SAMEPERIODLASTYEAR or DATE formats due to constraints with how data is reported. 
 
Desired end result:

Tablename: Subgroup

WKYR    TY   LY   LASTWKYR
202033    1000     0   201933
202133    3200   1000   202033

 

  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Haven't you just got your second and third arguments the wrong way wrong?

    LY =
LOOKUPVALUE ( Subgroup[TY], Subgroup[WKYR], Subgroup[LASTWKYR], 0 )

    Regards

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Haven't you just got your second and third arguments the wrong way wrong?

    LY =
LOOKUPVALUE ( Subgroup[TY], Subgroup[WKYR], Subgroup[LASTWKYR], 0 )

    Regards

    • AlexanderPrime's avatar
      AlexanderPrime
      Icon for Solution Supplier rankSolution Supplier
      4 years ago

      Well now I feel dumb, thanks Jos!

      (I blame it on a bad case of the Fridays!)