Mac Numbers Misbehaving; giving completely wrong results using Index and Xmatch

Question

Mac Numbers Misbehaving; giving completely wrong results using Index and Xmatch

I made this perfectly good, small calculator using Excel functions. I have a rate sheet on one tab, and on a separate tab I have this mini calculator, where I can enter the country (row across) and the weight of the package (column down) and it will return the correct postage for that package and country.

Exporting this to Numbers has been a nightmare. First, the functions break but, that's ok, I don't mind crawling down that rabbit hole to create a completely new formula using Index and XMatch (apparently, Match does not work for more than one criteria in Numbers). That only took a couple of hours (embarrassingly, but I never use Numbers for this so had to figure out how to create functions in that tiny little floating thing).

And, I got it! I figured it out! Except it returns screwy numbers. From other columns. I have tried changing the range to include and exclude what I try to imagine the app will find objectionable, and all I have figured out so far is that if I include the first weight entry of 0.5 (pounds) it shifts everything up one. So, I had to exclude that (not my biggest problem, but I would love to have an answer for that, too).

I am lost, here. It has, say, 75 columns for countries, but it will only search one column, and even then it comes up with the wrong number for that weight in that column.

I'll upload some screenshots to see if it makes any more sense.

This first one is just the top corner of the rate sheet:

This one shows a correct amount for GB, and an incorrect amount for AU (you can reference on the chart above)

This shows the formula for the GB column:

This shows the formula for the AU column. I realized they were slightly different so I changed them out...

Now, I believe they are the same. Notice how the amount for AU changed!

By the way, the column with $465.05 was an experiment with just the XMATCH function, a suggestion I saw online. Even though I had limited the decimal places to two, it still reads the full number, apparently, so I had to choose -1 instead of exact match to get a result, because I think it was rounding up. I did try the same -1 with the others, but no good result.

If I mess with the formula, like which rows are included, I do get slightly different answers for everything but the GB column.

Am I crazy here? Totally off? Using the completely wrong formulas? Help!!

Thanks,

Debbie

[Edited by Moderator]

MacBook Pro 16″, macOS 14.7

Posted on Sep 14, 2025 06:09 PM

Reply

Answer 1

SGIII

Level 8

37,226 points

Sep 14, 2025 07:45 PM in response to Debbie Shayne

Not sure exactly what is going wrong. Your table setup is "Excelsy." It's usually more efficient to have your data in one table and your "calculator" in a separate table on the same sheet. Here is a working example:

The formula in B4 of the calculator table:

=OFFSET(Data::$A$1,XMATCH(B3,Data::A)+1,XMATCH(B2,Data::$2:$2)−1)

OFFSET here works similarly to INDEX but I find it simpler to use because you just need an A1 "anchor" and don't need to change the ranges within the formula if you add or delete rows or columns. Data::A refers to the entire column A. Data::$2:$2 refers to the entire Row 2.

The +1 and -1 adjust for the presence or absence of Header Rows and Columns. I just played with the adjustments until I consistently got the correct results.

Rows 1 and 2 here are defined as Header Rows.

More on the functions here:

OFFSET - Apple Support

XMATCH - Apple Support

SG

Reply

Answer 2

Sep 14, 2025 10:22 PM in response to Debbie Shayne

Oh, wow, thank you! I will try that. And it looks "excelsy" most likely because I imported from Excel, which is what I use, but my shipping person uses Numbers, so I always have to convert. This is the first time I have had such a weird problem.

Many thanks, I will report back after trying it.

Reply

Answer 3

Badunit

Level 7

29,994 points

Sep 15, 2025 03:38 AM in response to Debbie Shayne

One thing I see is that INDEX is supposed to be INDEX(range, row-index, col-index) but you have row-index and col-index swapped. I can't get the results you are getting, partly because I can't tell which formula goes with which screenshot/result, but it is likely all the oddness is due to this one problem.

I don't know what problem you were having with MATCH vs XMATCH. The way you are using them with regular range references (like A3:DO69) you should get the exact same results from either. They give different results if you use "whole column" or "whole row" references (like A or $2:$2) because XMATCH doesn't count headers while MATCH does.

Reply

Answer 4

Sep 15, 2025 12:01 PM in response to Badunit

Bingo! Switching the row and column arguments fixed the problem. Thank you so so so much.

As for Match vs XMatch...maybe they would have worked the same. I was trying everything, and I think I misinterpreted the warning in Match that said it was one-dimensional (or, something like that).

Now I can get on with my work!

Reply