Trying to Make XLOOKUP Work with Single Cell Array

[jase71ds]

I'm trying to use XLOOKUP to reference a single cell (array) and return a value.

Example
A1 = .6
B1 = {0, .1, .6, .9, 1}
XLOOKUP( A1, B1, B1, , , -1 )

So if A1 were .6, then .6 would be returned
But if A1 were .5, then .1 would be returned

I understand that I could take cell B1 and stretch it out from B1 to B5, but I'm wondering if XLOOKUP can work with a single cell array.

Anyway, I've had no luck getting XLOOKUP to work with a single cell array. Am I doing something wrong, or is this just not possible?
Thanks.

 

[JamesCanale]

=XLOOKUP(A1,B1#,B1#,,-1)

 

[jase71ds]

=XLOOKUP(A1,B1#,B1#,,-1)

Hmmmm...
I get a #REF error.
To be clear, in B1, I literally just typed "{0, .1, .6, .9, 1}" (without the quotes)
Isn't this the correct way to enter an array value?
Any thoughts?

 

[JamesCanale]

I see. If you put in the '=' before it all, it will make an array from B1 to G1 - and then it will work.

If you can't SPILL into those other cells - then it is a different story that I can think about...

 

[Fluff]

B1 is just a text string, not an array, so you either need to split it into separate cells, or coerce into an array within the formula

 

[jase71ds]

B1 is just a text string, not an array, so you either need to split it into separate cells, or coerce into an array within the formula

Agree. Any thoughts on how to coerce it?

By the way, when I hardcode the formula as =XLOOKUP( A1, {0, .1, .6, .9, 1}, {0, .1, .6, .9, 1}, , -1 )
It works.
But if i put {0, .1, .6, .9, 1} in B1, and use your method of =XLOOKUP(A1,B1#,B1#,,-1) I get the #REF error.

 

[Fluff]

Agree. Any thoughts on how to coerce it?

Personally I wouldn't, I'd put the values in separate cells.

 

[JamesCanale]

This is a bit funny. Take out the {}:

MrExcelPlayground4.xlsx
	A	B
1	0.6	0, .1, .6, .9, 1
2	0.6
Sheet31

Cell Formulas
Range	Formula
A2	A2	=XLOOKUP(A1,FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),"",-1)

 

[jase71ds]

This is a bit funny. Take out the {}:

MrExcelPlayground4.xlsx
	A	B
1	0.6	0, .1, .6, .9, 1
2	0.6
Sheet31

Cell Formulas
Range	Formula
A2	A2	=XLOOKUP(A1,FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),"",-1)

Holy cow!!
It works, but it's scary quantum physics or something
I'll need to study this.
Thanks!!!

 

[JamesCanale]

I'm afraid of the FILTERXML function, but it does some things neatly.