Having trouble developing a formula that will target a value within an array based on 3 criteria . This screenshot is a simplified version of the original file . Lower left corner explains what Im trying to do but I have had trouble using XLookup, Match etc This is my first Mac.
[Re-Titled by Moderator]
iMac 24″, macOS 15.1
Interesting conundrum.
There are a couple of ways of going about this.
First up, though, you will simplify your Numbers life significantly if you use separate tables for your data and inputs.
If you're coming from Excel, then a 'sheet' equates to a 'table' and everything in in one huge grid.
In Numbers, though, a single Sheet can have multiple Tables, and this makes it really easy to separate the data (such as your start/end times) from your input and lookup values. Not critical, but I took this approach in recreating your problem.
I solved this by separating out the two XLOOKUPS - one XLOOKUP finds the ROW() that matches the caliber, the other XLOOKUP() finds the COLUMN() that matches the powder:
For example, the formula:
=ROW(XLOOKUP($B$4,Data::A,Data::A,"",-1,1))
will return the ROW() in the Data table that matches the caliber value entered in B4
Similarly, the formula:
=COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,-1))
will return the column number in the Data table that matches the powder value in cell B10
(note that when using Merged Cells, COLUMN() returns the lowest row/column number, so the lookup returns '2', even though the result spans columns 2 and 3)
Once you have these, you can use them as arguments to an INDIRECT() function to perform the lookup:
=INDIRECT("Data::R"&ROW(XLOOKUP($B$4,Data::A,Data::A,""−1,1))&"C"&COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,−1)),FALSE)
(for clarity, you could also used two hidden cells to track the ROW() and COLUMN() functions to simplify this)
The same function can be used for the Max lookup (cell D13), just increment the COLUMN() value by 1 to get the adjacent column:
D13:
=INDIRECT("Data::R"&ROW(XLOOKUP($B$4,Data::A,Data::A,"",1,1))&"C"&1+COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,−1)),FALSE)
Interesting conundrum.
There are a couple of ways of going about this.
First up, though, you will simplify your Numbers life significantly if you use separate tables for your data and inputs.
If you're coming from Excel, then a 'sheet' equates to a 'table' and everything in in one huge grid.
In Numbers, though, a single Sheet can have multiple Tables, and this makes it really easy to separate the data (such as your start/end times) from your input and lookup values. Not critical, but I took this approach in recreating your problem.
I solved this by separating out the two XLOOKUPS - one XLOOKUP finds the ROW() that matches the caliber, the other XLOOKUP() finds the COLUMN() that matches the powder:
For example, the formula:
=ROW(XLOOKUP($B$4,Data::A,Data::A,"",-1,1))
will return the ROW() in the Data table that matches the caliber value entered in B4
Similarly, the formula:
=COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,-1))
will return the column number in the Data table that matches the powder value in cell B10
(note that when using Merged Cells, COLUMN() returns the lowest row/column number, so the lookup returns '2', even though the result spans columns 2 and 3)
Once you have these, you can use them as arguments to an INDIRECT() function to perform the lookup:
=INDIRECT("Data::R"&ROW(XLOOKUP($B$4,Data::A,Data::A,""−1,1))&"C"&COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,−1)),FALSE)
(for clarity, you could also used two hidden cells to track the ROW() and COLUMN() functions to simplify this)
The same function can be used for the Max lookup (cell D13), just increment the COLUMN() value by 1 to get the adjacent column:
D13:
=INDIRECT("Data::R"&ROW(XLOOKUP($B$4,Data::A,Data::A,"",1,1))&"C"&1+COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,−1)),FALSE)
Another way, a bit more concise, is to use the OFFSET function with XMATCH:
In D12:
=OFFSET(Data::$A$1,XMATCH(B4,Data::$A)+1,XMATCH(B10,Data::$1:$1))
In D13:
=OFFSET(Data::$A$1,XMATCH(B4,Data::$A)+1,XMATCH(B10,Data::$1:$1)+1)
OFFSET starts from an "anchor" (here A1) and counts down and right.
The first XMATCH tells it how far down, the second XMATCH how far to the right.
Note that the second formula (for Max) is the same as the first (for Start) but has a +1 in the second XMATCH telling OFFSET to count one more to the right.
More on those functions here:
SG
It looks from your screenshot as if you've put a blank row at the top of your DATA table. Either delete that row or have the formula refer to DATA::$2:$2 instead of DATA::$1:$1. I'd remove the row, because in Numbers you don't need blank columns and rows to "pad" things the way you do in Excel. You can easily have multiple tables on one sheet. If you haven't done so already have a look at the templates at File > New in your menu. There are many excellent examples there of effective document design in Numbers.
SG
TO SG
Thanks for your patience, I tried separating the data based on your previous advice but was having difficulty. Im still not clear as to why 1 Table could not work.
Anyway I have now separated the data into 4 separate tables, the original underlying Table 1 from which cells D7-N7 and R9 selects measurement values associated with the bullet selection in Cell A6. The list of bullets is now in a new one column Table 1-1::, Cells A1 to A23. The second new Table is Table 2 which houses the Powder selection in cell A 2 and the Start and Max powder Loads derived from the Index Match formula. the third new Table (Table:: 3) is the list contains the Start and Max Powder loads depending upon the The bullet and Powder selected. The Start and Max Load are dependant upon the Bullet and Powder selected. The measurements in Table 1 Cells highlighted in yellow are also common to the bullet selected.
It is to work like this.
In Table 1 cell A6 a bullet is selected from new Table 1-1. column A. The XLOOKUP formulas in the adjoining cells D7-N7 and R9 looks up the value associated with cell A6 from the Table 1 cells B29:I50 B2 and aligned with the bullet Selection in Table 1-1 .
The type of Powder is selected in Table 2 cell A2 from the list in Table 3 line 1:1 . From the Index Match formula in Table 2 Cell B2 (Start value)and Cell F2 (Max value) the formula locates and reports the values aligned with the bullet (Row) and powder (Column) selections.
I will forwarding 2 screenshots showing the whole page.
Have you tried doing this "Numbers style"?
Rather than one big expanse of cells separate your work into distinct tables as in the examples above.
Once you've done that repost here and we can have another look.
SG
> been another 10 years figuring that out. One
Ooops, yes. Looks like it didn't paste correctly.
=INDIRECT("Data::R"&ROW(XLOOKUP($B$4,Data::A,Data::A,"",−1,1))&"C"&COLUMN(XLOOKUP($B$10,Data::$1:$1,Data::$1:$1,"",0,−1)),FALSE)
First of all thank you for your pointers and the speedy response, My 77 yo brain would have been another 10 years figuring that out. One question Are the 2 INDIRECT formulas cut off at the end? ie "" and Closed brackets?
Thankyou,
Your formula appears to have worked in the example but I suspect I somehow messed something up as it did not work for me. I changed one cell, what had been Cell B10 became Cell B7 which I simply substituted in the formula and I added 2 more columns to the data table which should not impact the results as the Data table is referenced to the upper left corner. The message in each of cells B4 and B7 indicates "XMATCH could not find the requested value". Ive checked the formulas, what am I doing wrong?
The attached table is supposed to work by selecting a bullet from cell A6 (= cell A44 in this case- Hndy 120 gr ELDM) and a powder type from cell B21 (= merged cell V29 -HVarget in this case) and delivering the intersecting Start and Max loads in array J32:Y51 into cells B23 and F23 respectively. The in between values are simply an extrapolation between the Start amd Max values. The cell formulas on Lines 25 were just for practice purposes in trying to resolve my problem.
I had received help from SGIII in developing a formula which did not work for me initially in the exact form provided for reasons I could never figure out but with a slight change I got it to work. That was originally on a practice Numbers file I had created. When I got it to work I substituted the formulas into the attached new file I created. The successful formulas were as follows:
In Cell B23 (Start)
=OFFSET($A$30,XMATCH($A$5,$A32:$51)+1,XMATCH($B$20,30:30))
In Cell F23 (Max)
=OFFSET($A$30,XMATCH($A$5,A32:$A51)+1,XMATCH($B$20,30:30)+1)
All worked well until I copied and pasted the entire contents into the original file that had been originally created in Excel on my old windows computer and opened on my new Mac. That original file contained several other “Workbooks” this new table being one of them. The new imported data ended up in a slightly different lline location creating an error however I thought it was just a matter of altering the cell references in the 2 formulas. THat proved not to be the case. Its now delivering incorrect data adjacent to or around the correct values. Ive tried everything such a altering the INDEX cell reference adding + 2 for + etc 1 but I cant get it to work. Ive watched several youtube videos that dont speak to the basics of the program. Part of the problem is that Ive never worked with the INDEX MATCH function.
With this particular bullet and powder combination it should be returning 36.0 and 39.2 however it delivers 39.2 and 44.2 in this case offset 1 column to the right. The results however are not consistent when entering different Bullet /Powder combinations. I dont know where to go from here.
Can you help me? I am certain the problem is simple to resolve but its beyond me at the moment.
If you haven't done so already be sure to spend some time with the User Guide at Help > Numbers Help in your menu.
Numbers doesn't have a "page." And I can't tell from your screenshot whether you've set up separate tables. It looks like one big expanse of cells.
In general, I suggest avoiding merged cells.
SG
Mac Numbers formula for array value based on 3 criteria
