Does IF always correctly recognise the output of XLOOKUP in Mac Numbers?

Question

Level 1

11 points

Does IF always correctly recognise the output of XLOOKUP in Mac Numbers?

I use a Summary table to extract data from a large number of sheets, each with many data tables. Originally the Summary table cells were simply populated using XLOOKUP formulae. However some of the data tables occasionally contain cells with missing data, I.e. blank cells, and, as shown in the following trivial example, this raised a problem.

The first table below is playing the part of one of my data tables..

Table 2 uses XLOOKUP() as in my original Summary table. The missing data from first table is interpreted as a 0.

In the sundry table I need to replace such missing data values with default values. Not sure whether it is relevant or not but the missing data and default values are always Dates.

I'm now trying to catch the missing data values with an IF statement but without success as shown in the following .

replace Col B formulae like

XLOOKUP($A2,Data::A,Date,"XXX",0,1)

with
IF(XLOOKUP($A2,Data::A,Date,"XXX",0,1)=0,Info::$B$1,XLOOKUP($A2,Data::A,Date,"XXX",0,1))

In Table 3 the IF statement seems not to recognise the 0 generated by XLOOKUP as my default value is not displayed in Row 3.

I'm hoping that someone here will be able to tell me 1) what is wrong with my IF(XLOOKUP( … formula and/or 2) whether there is another strategy to achieve the desired outcome.

-JBH2

MacBook Pro 16″, macOS 12.6

Posted on Jan 28, 2023 2:15 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,456 points

Jan 29, 2023 6:00 AM in response to jbh2

It does appear that the result of XLOOKUP is a strange result if the cell it returns is empty. I tried =0 and ="" and even tried comparing it to the cell itself and it is not equal to any of them. But it is equal to itself and its length is zero so check the length instead:

IF(LEN(XLOOKUP($A2,Data::A,Date,"XXX",0,1))=0, therestofyourformula)

Note that if in your XLOOKUP the "not found" value was "" instead of "xxx", the length of that would also be zero.

Reply

Answer 2

SGIII

Level 8

37,550 points

Jan 28, 2023 6:10 AM in response to jbh2

Not exactly sure why the formula doesn't work. In any case, it might be easier to do your lookup on a fully populated column in the data table. Add an extra column and fill it with a formula like this:

In E2, filled down:

=IF(D2="",Info::B$1,D2)

In B2, filled down:

=XLOOKUP(A2,Data::A,Data::E,"XXX")

SG

Reply

Answer 3

Badunit

Level 7

30,456 points

Jan 29, 2023 7:03 AM in response to Badunit

Here is another way to do it. It first turns the XLOOKUP result into a string by appending "" to it then compares it to "":

IF(XLOOKUP($A2,Data::A,Date,"XXX",0,1)&""="", therestofyourformula)

Reply

Answer 4

jbh2 Author

Level 1

11 points

Jan 28, 2023 3:04 PM in response to SGIII

Thanks for responding. I don’t want to mess with my input data tables (as mentioned, there are lots of them spread over many sheets) so instead of including XLOOKUP in the IF function I’ll use a hidden column in the summary table to get the XLOOKUP output. This works for me because the data are dates and I don't have to distinguish an actual 0 value from a zero derived from from missing data.

But do you think I should report the failure of IF to correctly recognise the 0 output of XLOOKUP as a flaw in the IF functionality?

JBH

Reply

Answer 5

SGIII

Level 8

37,550 points

Jan 29, 2023 6:09 AM in response to Badunit

jbh2 wrote:

do you think I should report the failure of IF to correctly recognise the 0 output of XLOOKUP as a flaw in the IF functionality?

It wouldn't hurt to go to Numbers > Provide Numbers Feedback in your menu and point out this apparent anomaly where testing for 0 or "" doesn't seem to work properly.

I believe making sure source data columns are properly populated is likely to save you trouble in the end, though, perhaps better than resorting to awkward workarounds that might hide a problem with the data.

SG

Reply

Does IF always correctly recognise the output of XLOOKUP in Mac Numbers?

Similar questions