Forum Discussion
Solved
Referencing Cell with Multiple Dates
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: ...
=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.
=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.
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.
=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.
=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.
- 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).