Mutil-critieria XLookUp with range is concacted together - not working

I am updating my Index/Match formulas to the new XLookup. My newly revised formula is this and works fine:

Please Login or Register  to view this content.

Basically it is saying when the date is less than Y3 and the date is greater than C3, then depending on H3, return when the K is > CN3 or L number < CN3. Return the first match's date.

Since I have 22 different worksheets (EURUSD is one of the 22), and every week I add to the worksheets. With the above formula I have to change the worksheet name and have to update the last row (in above case it is 100286).

So I created a concactate formula to replace "EURUSD!$A$3:$A$100286" with a dynamic one that will change with the worksheet name and number of rows automatically.

Here is what I came up with:

Please Login or Register  to view this content.

I also tried CONCATENATE($G3,"!$A$3:$A$",$U$1), but same results, neighter one works. If I put the CONCATENATE($G3,"!$A$3:$A$",$U$1) in a cell, it will result in "EURUSD!$A$3:$A$100286". So that works.

So what am I doing wrong that it will not read my concatenate correctly? I appreicate your advice in advance.

If CONCATENATE($G3,"!$A$3:$A$",$U$1) gives you the equation you are looking for try INDIRECT(CONCATENATE($G3,"!$A$3:$A$",$U$1)) If it does not work then check the fx on the formula bar to see what it is "understanding" and adjust your concatenate to the correct format.

Originally Posted by Squeaky

If CONCATENATE($G3,"!$A$3:$A$",$U$1) gives you the equation you are looking for try INDIRECT(CONCATENATE($G3,"!$A$3:$A$",$U$1)) If it does not work then check the fx on the formula bar to see what it is "understanding" and adjust your concatenate to the correct format.

Thank you for the few suggestions.

Indirect gives a !Spill.

I loaded in fx by itself, and attached is the result. It looks good, but it is surrounded by " ". I was thinking that could be the problem. It sees it as text and not a valid range reference. Could that be it?

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.

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

Then change the FILTER formula to

Formula:

Please Login or Register  to view this content.

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.

I looked at some of my indirect formulas and what I see is mine also have the "" around them and they work. What I do not see in yours is the ' marks around 'Eurusd'. inside the indirect formula put "'"&$G4&"'"&"!A$3:$a$"&$U$1
While it is true if you have many indirect formulas it will cause a slowdown, a few may not. You can only see how it works on your spreadsheet to determine that.

Originally Posted by Squeaky

I looked at some of my indirect formulas and what I see is mine also have the "" around them and they work. What I do not see in yours is the ' marks around 'Eurusd'. inside the indirect formula put "'"&$G4&"'"&"!A$3:$a$"&$U$1
While it is true if you have many indirect formulas it will cause a slowdown, a few may not. You can only see how it works on your spreadsheet to determine that.

Oh wow, Squeaky. Good eye. You are right, that was the problem. Thank you very much!