How can I create an XLOOKUP formula with 2 search criteria in Numbers?

Question

Aloohaa Author

Level 1

8 points

How can I create an XLOOKUP formula with 2 search criteria in Numbers?

Hi,

How can I create an XLOOKUP formula with 2 search criteria?

I have managed to create a working XLOOKUP formula with one search criterion:

XLOOKUP(B4;B7:B10;C7:C10;0)

but with 2 search criteria I don't want to succeed:

XLOOKUP($A$4&$B$4;$A7:$A10&B7:B10;C7:C10;0)

As I use a German version of Numbers the function corresponding to XLOOKUP is XVERWEIS.

Since I don't see a way to attach a sample file, I'll post a screenshot of it.

Kind regards

Aloohaa

[Edited by Moderator]

Original Title: XLOOKUP with 2 search criteria

Mac mini

Posted on Jul 28, 2025 9:06 AM

Reply

Answer 1

Badunit

Level 7

30,336 points

Jul 28, 2025 2:07 PM in response to Aloohaa

XLOOKUP uses one criteria. You would have to make a new column that combines the date and name and use that column for the lookup. But there are a few other ways to do it without XLOOKUP.

Because you are looking for a number, you can use MAXIFS, MINIFS, or SUMIFS. These accept 1 or more criteria.

If you have Numbers 14.4 you can use the new FILTER function.

B4 =MAXIFS(B7:B10,A7:A10,A4,C7:C10,C4)

C4 =@FILTER(B7:B10,(A7:A10=A4)×(C7:C10=C4))

D4 =SUMIFS(B7:B10,A7:A10,A4,C7:C10,C4)

MAXIFS will return the higest value that matches the criteria

MINIFS will find the smallest value

SUMIFS will sum all matches

FILTER can give you all matches in separate rows if you take the @ off the front of the formula. The @ is making it give only the first match.

The design of your table (and my re-creation of it) is kind of a mess. It is better to dedicate the table to the data and do the lookup stuff elsewhere, like in a different table (but you could do it in a header or footer)

Reply

Answer 2

Badunit

Level 7

30,336 points

Jul 29, 2025 4:37 AM in response to Aloohaa

I'm sure you figured out it was B4, B5, B6 instead of B4, C4, D4.

Often when someone has learned in Excel, they construct tables the same way in Numbers. So we usually try to point out the "Numbers way" to do things. But you already knew.

Reply

Answer 3

Aloohaa Author

Level 1

8 points

Jul 29, 2025 3:45 PM in response to Aloohaa

It's not that I'm just looking for a number. I am searching for a name using a number and the date.

The real situation is that I open a table exported from online banking in .csv format in Numbers.

It contains a series of columns and each row contains a transaction on the account, starting with the date, a code, the amount (Zahl), the recipient's name (Empfänger), the recipient's account, the description of the transaction (Betreff), etc.

And I would like to convert the data of any line into a different format using 2 search criteria, date and amount, if required.

So I can't do anything with formulas like SUM that do calculations.

I am attaching 2 handmade sheets to represent what I want to do. However, they are in German, but I think the idea is still recognizable.

The first shows how I want to display the data using the 2 criteria date and number, the second shows a search mask and the database (Datenbank).

[Edited by Moderator]

Reply

Answer 4

Aloohaa Author

Level 1

8 points

Jul 29, 2025 3:30 PM in response to Aloohaa

I have finally managed to modify the FILTER formula so as to get the desired data, if present on the same sheet.

But I have problems when including cell references of another sheet into the formula: if I refer to a cell in sheet 1 then the name of the file is written into the formula instead of de name of the sheet; however if I refer to a cell in another sheet than sheet 1 then the name of the sheet is written in. Strange.

Reply

Answer 5

Badunit

Level 7

30,336 points

Jul 30, 2025 5:52 AM in response to Aloohaa

I don't know where I got the idea you were looking for a match of date&name and retrieving a number. Looking now it is obvious that is not what you were doing.

I don't think functions have any access or knowledge of the filename of your Numbers document so your statement about "the name of the file" being in the formula is surprising. It would be great if Numbers had that ability if it meant it could retrieve data from other Numbers files (something that has been asked about and requested many times here). Maybe they've been working on it. Can you post a screenshot of this with the filename and sheet tabs included at the top?

Reply

Answer 6

Aloohaa Author

Level 1

8 points

Aug 9, 2025 5:18 AM in response to Aloohaa

I have managed to customize the FILTER formula so that I can display the desired data, with the search criteria date and amount (Montant).

As I said, when I make a reference to the sheet containing the database in the sheet "Données", the name "Données" is not displayed but that of the file, as shown in the formula.

To illustrate this further, I have made a simple reference (=) to a cell in "Données" in C10 (the formula can be seen in D10), and one in C11 to a cell in "Blatt 1" (the formula can be seen in D11).

Strange, but the main thing is that it works.

[Edited by Moderator]

Reply

Answer 7

Aloohaa Author

Level 1

8 points

Jul 30, 2025 2:53 PM in response to Badunit

It has no name, probably because I deleted it because it bothered me, I don't remember.

And that seems to be the cause: in order to test that I made a new file with 3 sheets; if I make a reference in sheet 3 to sheet 1 and sheet 2, then the name of the sheet and the table appears and no longer that of the file. Strange though that it works like that!

Reply

Answer 8

Badunit

Level 7

30,336 points

Jul 30, 2025 4:33 PM in response to Aloohaa

All tables have a name. If you deleted it, that didn't really delete it, it just hid it from view. I am thinking the table has the same name as the file. You can see it by clicking the disclosure triangle for that sheet. Or you can select the table, go to the sidebar on the right, click on the Table tab, then check the box for "Title" so you can see it and then edit it.

Reply

Answer 9

Aloohaa Author

Level 1

8 points

Jul 31, 2025 1:36 AM in response to Badunit

In the sidebar, the title is “Tabellentitel 1”.

If I make another sheet, the table is called “Tabelle 1” and in the sidebar it is also called “Tabellentitel 1”.

I have now opened another file of the same type (exported as .csv from online banking); no table name is displayed above the table (so I had not deleted it in the first one) and if I insert another sheet and make a = reference to a cell in the imported one then the name of the file is also displayed in the formula instead of that of the sheet Blatt 1 and the table name, the table name not being displayed above the table.

---------UPDATE--------

Without any action on my part - I don't understand where it suddenly came from - the name of the file suddenly appeared above the table in "Données", so that the name of the table and the file are actually identical. I have changed the table name and the formulas adapt: it is also changed there.

But, since it works with 2 identical names, it doesn't really matter.

Reply

Answer 10

Aloohaa Author

Level 1

8 points

Aug 1, 2025 5:45 AM in response to Badunit

I rejoiced too soon: since I changed the table name of Données, the new name is taken over in the formulas, but they only output 0, so they work, otherwise a white exclamation mark in a red triangle would be output, but they don't output the right value.

Reply