Help with xlookup, lookup array from multiple columns

[Kaimre328]

Hi all,

I'm having trouble with an xlookup formula for a project I'm working on. I want the lookup array to be A2 and column B but every time I try to do this it says spill error. This is the formula I've used: 'xlookup(A3 % 10 B:B, '10'! F:F, 10! B:B, "N/A).

 

[awoohaw]

I'm not familiar with your syntax, what does the "% 10" do in your formula.
does your version of excel have Textjoin function?

Additionally, using entire column lengths is, by many, not considered a best practice. It works fine in smaller spreadsheets, but can slow your workbook down with larger ones. Have you considered using Tables?

 

[Fluff]

Are you actually using 2019 as that version does not have the Xlookup function, nor does it have spill ranges?

 

[Kaimre328]

Apologies that should be & not %. Il check re textjoin

 

[awoohaw]

also, the arrays must be the same length, so if you add the lone cell in A3 to B:B, the arrays is one more in length that your look up array F:F.

Or is the lookup value in A3?

 

[awoohaw]

here are two ways to try. One is older functionality, one is XLOOKUP, which may not be available in your version:

Book1
	A	B				F
1
2	lookup:	NF				17
3	QB	NX				34
4	INDEX-MATCH	AR				10
5	49	TD				44
6	XLOOKUP	XW				26
7	49	QB				49
8		KA				17
9		AS				9
10		IO				45
11		GE				34
Sheet1

Cell Formulas
Range	Formula
A5	A5	=INDEX(F2:F11,MATCH(A3,B2:B11,0))
A7	A7	=XLOOKUP(A3,B2:B11,F2:F11)