This site is acting up, so I have to avoid using the less than character.
If you have XLOOKUP, your Excel version isn't 2010.
If you have XLOOKUP, you should also have FILTER. FILTER is more useful than XLOOKUP. By far.
If there were multiple rows satisfying the criteria, FILTER would return them all. Thus the need for the INDEX(FILTER(...),1) call to return the 1st one. If there are no rows satisfying the criteria, FILTER with no 3rd argument returns the new #CALC! error, and IFERROR converts that into "Not Found". The advantage of using FILTER is that you'd have the option of also returning the 2nd, 3rd, etc rows satisfying the criteria.
As for adding more rows to worksheets, you could use worksheet-level defined names. For example, if there should be dates in col A for all rows with data, define Bottom for worksheet EURUSD referring to the formula =MATCH(1,0/ISNUMBER(EURUSD!$A$3:$A$1048576)), and define Dates for worksheet EURUSD referring to the formula =EURUSD!$A$3:INDEX(EURUSD!$A$3:$A$1048576,Bottom). Similarly for the other columns. For my own simplicity I'll use ColK and ColL for the ranges in columns K and L. Then
As for using different worksheets, you could do that with 1) a list of worksheet names and 2) workbook-level names. Assuming the list of worksheet names were in a range with workbook-level name WSListG (the G stands for Global, meaning workbook scope) looking something like
CADEUR
CADUKP
CADUSD
EURUKP
EURUSD
UKPUSD
and so on. I'll assume the worksheet name you want to use is in cell whatever!D1, where whatever should be replaced by the name of the worksheet in which you're entering these formulas. I'd recommend using a workbook-level defined name WSIndexG referring to the formula =MATCH(whatever!$D$1,WSListG,0). Then workbook-level defined names like DatesG referring to the formula
Then change the FILTER formula to
The point of all this is to avoid INDIRECT or other volatile functions. I figure you'd need a fair number of these formulas, and each of those INDIRECT calls referring to ranges of more than 100,000 rows would KILL recalc performance. What's above is the best way I've found to avoid volatile functions for dynamic worksheet addressing for a KNOWN or KNOWABLE set of worksheets. The main thing to understand is that the appearance of ranges (names of actual references) in CHOOSE function calls must match the order of worksheet names in WSListG.
Bookmarks