Complex look up formula having both vertical and horizontal lookup areas

[zaska]

Hello,

I want to retrieve the data of each lookup symbol for various dates. The problem here is that the dates are arranged horizontally whereas the symbol names are arranged vertically in the lookup area. How can I retrieve the data using a formula. I tried using the xlookup but failed to get the values. I am herewith attaching the sample data

=XLOOKUP(1,(A10=RawData!B3:X3)*(Analysis!$H$2=RawData!$A$4:$A$13),RawData!$B$4:$X$13)

Thank you

[AskMeAboutExcel]

Sumproduct should do the trick

=SUMPRODUCT(   RawData!$B$4:$X$13,    (RawData!$A$4:$A$13 = Analysis!$H$2)  * (RawData!$B$3:$X$3 = Analysis!$A10))

[AskMeAboutExcel]

or alternatively a nested lookup

=XLOOKUP(  $H$2,  RawData!$A$4:$A$13,  XLOOKUP( $A10,  RawData!$C$3:$X$3, RawData!$C$4:$X$13 ) )

[Glenn Kennedy]

Option 1 (copied down):
=INDEX(RawData!$B$4:$X$13,MATCH(Analysis!$H$2,RawData!$A$4:$A$13,0),MATCH(Analysis!A10,RawData!$B$3:$X$3,0))

Option 2 (spill array):
=BYROW(A10:A30,LAMBDA(x,INDEX(RawData!B4:X13,MATCH(H2,RawData!A4:A13,0),MATCH(x,RawData!B3:X3,0))))

[popipipo]

=INDEX(RawData!$B$4:$X$13,MATCH(Analysis!$H$2,RawData!$A$4:$A$13,0),MATCH(Analysis!$A10,RawData!$B$3:$X$3,0))

Try this index/match formula.

[AskMeAboutExcel]

Four different methods to get to the same results

Out of interest, has anyone done analysis with large datasets to see how they compare on performance overhead? Conventional wisdom is that index/matches are optimal - but I am curious against LAMBDAs and XLOOKUPs