Feb 26 2022 01:11 AM
Hello, I want to return the price based on lookup values (dates in horizontal) and (symbol in vertical) just like the format in the right from the data source which is on the left. Both are different sheets. Data are auto-updated in the source sheet from the folder.
Using the below Xlookup formula I only got value for the first date i.e (7/1/2021), didn't get it for others.
I want to create a referential formula that extracts data just like the table in right.
=XLOOKUP(G$1,$A$2:$A$17,XLOOKUP($E2,$B$2:$B$17,$C$2:$C$17))
Thanks
Feb 26 2022 02:01 AM
=VLOOKUP(F$1&$E2,CHOOSE({1,2},$A$2:$A$17&$B$2:$B$17,$C$2:$C$17),2,0)
Maybe with this formula as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=SUMPRODUCT(($A$2:$A$17=F$1)*($B$2:$B$17=$E2)*$C$2:$C$17)
An alternative could be this formula.
=INDEX($C$2:$C$17,MATCH(F$1&$E2,$A$2:$A$17&$B$2:$B$17,0))
Another alternative could be INDEX and MATCH. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Feb 26 2022 02:08 AM
@RohitJung As a variant, use FILTER with structured table references.
=FILTER(Table1[[Price]:[Price]],(Table1[[Date]:[Date]]=F$1)*(Table1[[Symbol]:[Symbol]]=$E2))
Feb 26 2022 05:49 AM
@Riny_van_Eekelen
Thank you very much for responding. I tried but couldn't make it.
The Dummy file is here where I tried this function.
https://filebin.net/f3y4a5gjwb3a084a
In real Data Source is auto-updated from the folder where daily excel records are stored, and the task is done in a different sheet.
Feb 26 2022 07:44 AM
@OliverScheurich Thank you for the effort. It works perfectly when both the data source and task are in the same sheet but the source of data is in different sheet and the task has to be done on a different sheet. I tried all three formulas with some modifications but couldn't make it. Can you please check where did I make a mistake... dummy file is attached in the link below:
https://filebin.net/cypmw8lspepkxu7x
Thankyou again :)
Feb 26 2022 09:29 AM
You are welcome. The dates in your sheet "Task" in range B1:AE1 are formatted as text. Therefore these dates aren't recognized as numbers and can't be used for calculations. The dates in your sheet "Datas" in range A2:A35334 are formatted as numbers and can be used for calculations.
In sheet "Task" the dates are left-aligned in the cells and in sheet "Datas" the dates are right-aligned. Text values are left-aligned in the cells and number values are right-aligned in the cells.
The solution is to enter the dates in your sheet "Task" in the same format as in sheet "Datas".
This works in my spreadsheet as you can see in the attached file. I have to enter all three formulas (INDEX, SUMPRODUCT and VLOOKUP) with ctrl+shift+enter because i don't work with Office365 or 2021.
Feb 26 2022 08:01 PM
@OliverScheurich
Thank you so much for replying.
I got it :)
Tried another formula that works
=SUMIFS(Datas!$C$2:$C$22,Datas!$A$2:$A$22,Task!F$1,Data!$B$2:$B$22,Task!$E2)