#CALC! error

this formula only returns 1 column

=XLOOKUP(
  C3:C70,
  allquestions[number],
  allquestions[[question]:[more3]],
  "Not found"
)

The reason your formula only returns one column is because of how XLOOKUP behaves when the Lookup Array is a range (C3:C70).

When you pass a range as the first argument to XLOOKUP, Excel performs a "vectorized" lookup. It looks at each item in C3:C70 one by one, but it expects to return one result per item. While XLOOKUP can return multiple columns, it often struggles to "spill" a 2D array (multiple rows AND multiple columns) when the input itself is an array.

To get the full table (all columns from [question] to [more3]) for every row in C3:C70, you have two options:

Option 1: The MAP approach (Recommended for 2D results)

If you want to return multiple columns for every row in your list, you must use MAP to force Excel to handle the "multi-column spill" for every single iteration.

However, to make it work with your large dataset, you must use the LET function to pre-filter the data so you don't get the #CALC! error again.

=LET(
   f_nums, FILTER(allquestions[number], allquestions[test]="ins"),
   f_data, FILTER(allquestions[[question]:[more3]], allquestions[test]="ins"),
   MAP(C3:C70, LAMBDA(q,
XLOOKUP(q, f_nums, f_data, "Not found")
   ))
)

Note: If this still gives a #VALUE! error, it means your version of Excel's MAP function is strictly limited to returning a single value per cell. If that happens, move to Option 2.

Option 2: The "Helper Table" approach (The most stable)

If you are working with 11,000+ rows and need multiple columns returned, the most "Excel-native" and fastest way to do this without hitting calculation limits is to avoid complex array formulas entirely.

1.Create a Filtered Table: In a new sheet (or a separate area), create a table that only contains the "ins" rows:

•Cell Z1: =FILTER(allquestions, allquestions[test]="ins")

2.Perform a standard Lookup: Use a standard XLOOKUP against that new, smaller table:

=XLOOKUP(C3, FilteredTable[number], FilteredTable[[question]:[more3]], "Not found") Then drag that formula down from row 3 to 70.

3 Reasons why Option 2 is often better for large data:

• Memory: Excel doesn't have to hold massive "virtual" arrays in its RAM; it's looking at actual cells on a sheet.

• Debugging: You can actually see the filtered data to make sure it's correct.

• Speed: Standard cell-based lookups are significantly faster than MAP/LAMBDA when returning multi-column arrays.