Referencing Cell with Multiple Dates

[arjungrewal]

Hi all,

Example of cells in sheet 1:

UnitID     Date                Value

test123    01/31/2023     1

test123    02/28/2023     2

test123    03/31/2023     3

Example of cells in sheet 2:

UnitID     01/31/2023   02/28/2023   03/31/2023

test123    

test123    

test123   

What would be the most efficient way to reference the values from sheet 1? I tried index and match, but I'm not being specific on what date when trying to match so excel doesn't know what month value to give. 

I know in sheet 2 I should use UnitID and [date column] to reference sheet 1 but having trouble coming up with a formula.

Thanks.

[#2 · Accepted solution]

@arjungrewal 

=IFNA(INDEX($C$4:$C$8,MATCH(1,(B$12=$B$4:$B$8)*($A13=$A$4:$A$8),0)),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

@arjungrewal 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.

[#4]

I used this formula but the value I'm getting is blank.
=IFNA(INDEX(Monthly!$D$2:$D$2000,MATCH(46550,(G$1=Monthly!$E$2:$E$2000)*($A23=Monthly!$C$2:$C$2000),0)),"")

I might just try the power query method you mentioned below.

[#5]

@arjungrewal 

=IFNA(INDEX(Monthly!$D$2:$D$2000,MATCH(1,(G$1=Monthly!$E$2:$E$2000)*($A23=Monthly!$C$2:$C$2000),0)),"")

This formula returns the intended result in the attached file. I've replaced 46550 with 1 in the formula.

[#6]

Sorry I don't know why I had 46550 in there. Must've accidently replaced the 1 when I was copying formula.

So, the formula does work now thank you! The reason it wasn't working before was because the date cell that I was referencing was a column. When I referenced the same date cell after I removed it as a column, the formula worked perfectly. I'm not sure why that is but thank you nonetheless (this was after I changed 46550 to 1).