XLOOKUP - [if not found] argument question

[cvrband]

I have an 'xlookup' formula in cell C4 that spills as expected to the required rows, but I'm having an issue with the [if not found] argument not returning the correct data. The data I want [if not found] to return is text in the same row from another spilled array starting in cell E4. (note: the spilled arrays from C4 and E4 always have the same number of rows) I can't think of a good way to present example data due to the complexity, so here is the gist of the formula in cell C4:

=XLOOKUP(D4#, lookup_array, return_array, "Not found:  "&E4#)

The issue I'm having is that the [if not found] argument only returns the text from cell E4 no matter what row is being calculated. I expected that the spilled formula in row 7 would return the text from cell E7 for [if not found], but it only returns the text in cell E4.

Simplifying the formula in C4 to the following also always results in the text from cell E4 being returned for [if not found]:

=XLOOKUP(D4#, lookup_array, return_array, E4#)

I also tried this with the same result:

=XLOOKUP(D4#, lookup_array, return_array, "Not found:  "&INDIRECT("E"&row()))

To troubleshoot, I tried placing

=INDIRECT("E"&row())

in another cell in row 7 with it returning the desired result, so I am very confused at this point.

What can I do to return the correct data for the current row [if not found]? Thanks

 

[Cubist]

Seems to work OK for me.

Book1
	A	B	C	D	E	F
1	Not found: 1	Lookup Array1	Return Array1		Lookup Array 11	Not found: 1
2	Not found: 2	Lookup Array2	Return Array2			Not found: 2
3	Not found: 3	Lookup Array3	Return Array3			Not found: 3
4	Not found: 4	Lookup Array4	Return Array4			Not found: 4
5	Not found: 5	Lookup Array5	Return Array5			Not found: 5
6	Not found: 6	Lookup Array6	Return Array6			Not found: 6
7	Not found: 7	Lookup Array7	Return Array7			Not found: 7
8	Not found: 8	Lookup Array8	Return Array8			Not found: 8
9	Not found: 9	Lookup Array9	Return Array9			Not found: 9
10	Not found: 10	Lookup Array10	Return Array10			Not found: 10
Sheet2

Cell Formulas
Range	Formula
A1:A10	A1	="Not found: " & SEQUENCE(10)
B1:B10	B1	="Lookup Array" & SEQUENCE(10)
C1:C10	C1	="Return Array" & SEQUENCE(10)
F1:F10	F1	=XLOOKUP(E1,B1#,C1#, A1#,0)
Dynamic array formulas.

 

[Fluff]

When looking up an array of vales, you cannot have an array of values for the Not found argument.

 

[RoryA]

You could do something like this:

=MAP(D4#,E4#,LAMBDA(d,e,XLOOKUP(d, lookup_array, return_array, e)))

 

[cvrband]

@RoryA - that solution does exactly what I need. Thank you for your time and expertise.

Thank you all for your responses!