XLOOKUP : returning multiple columns

It is easy to get that result, just not like Excel does it. Numbers does not have that shortcut way of doing formulas to create "arrays" of values.

Instead of cell G2 =XLOOKUP(F2,A2:A7,B2:D7)

it is

cell G2 = XLOOKUP($F2,$A2:$A7, B2:B7)

Fill right to the other columns.

or doing it the Numbers way

cell G2 = XLOOKUP($F2,$A,B)

fill right to the other columns.

You can also fill this downward to search on other names.

Perhaps what you are missing is that Numbers is not an MS Excel clone.

XLOOKUP is a relatively recent addition to Numbers supported functions. "multiple return columns does not appear to be a supported action in the Numbers version.

Image below is the complete set of examples provided with the Function Browser article on XLOOKUP.

Function Browser contains articles explaining the purpose, syntax and properties of every function supported in Numbers.

Select any cell in a table, then press = to open the Formula Editor and the Function Browser.

In the Browser, which opens in the right sidebar, typing as many characters as necessary to identify the function (two is enough for XLOOKUP) takes you to that function's page in the Browser.

If multiple returns at one go is a feature you want to see in Numbers, use the Provide Numbers Feedback menu item in the Numbers menu to make a Feature Request.

Feedback items go directly to Apple (and the production teams at Apple).

Apple provides the space for these Apple Support Communities, but has limited participation in these user-to-user communities, none of it by members of the appliction production team members.

Regards,

Barry

Regards,

Barry

Like this? It will return an array of values from the matching row. I used INDEX to access the second value. You could instead use SUM to sum them or use MAX or MIN or AVERAGE or other functions that operate on ranges/arrays.

Hi, can you please suggest me, how to search in many columns and rows?

XLOOKUP: searching multiple columns - Apple Community

Thanks