Lookup Value Horizontally

John1232 

I am confused by the expected results from your screenshot.

Row 1 of Expected Results shows Answer = Value 3

   - WHERE Unique Value = 1 AND Date = 3/3/2023

   - However, this does not match the source Lookup table.  Answer should be Value 1.

Row 2 of Expected Results shows Answer = Value 3

   - But again, based on the source Lookup table, the answer should be Value 1.

Row 3 shows Answer = Value 2, but should be Value 3.

Only Row 4 appears to be correct with Value 2.

Can you help me understand how you are calculating these values that seem to be incorrect?

NOTE 1: You mention that when you unpivot, it creates "duplicates" in the unique value column and will no longer support a relationship.  However, these are not true duplicates.  They actually reflect the data that is already there prior to unpivoting.  If you leave the data as is, you still cannot use Unique Value by itself to get your Answer.  It must be a combination of 2 items: Unique Value + Date.  This will be true regardless of whether you unpivot or not.  

NOTE 2: Due to two date columns being identical (3/14 & 3/14), you actually do have 4 instances of true duplicates:

Are these valid duplicates?  Or do they need to be corrected?

Can you provide a small sample data set for the other table you need to join to?
Does that table have both the "Key" as well as the Date?

(I say "Key" because this column is not a true Key.  The actual Key is Key + Date.)

Regards,

Nathan