Return value from 2 criteria lookup array, based on list of horizontal and vertical lookup value.

Copper Contributor

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))

RohitJung_2-1645866529338.png

 

Thanks

 

 

 

6 Replies

@RohitJung 

=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.

@RohitJung As a variant, use FILTER with structured table references.

=FILTER(Table1[[Price]:[Price]],(Table1[[Date]:[Date]]=F$1)*(Table1[[Symbol]:[Symbol]]=$E2))

Riny_van_Eekelen_0-1645870030771.png

 

@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. 

@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 :)

@RohitJung 

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.

@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)