Help with Numbers LOOKUP.

Is that an actual screenshot? If so, the first problem is your cell references are wrong - or, at least, I can't see how you're structuring your data.

In the table:

the cell with the error looks like B3. If its formula is =LOOKUP(A2...) then its looking for the value "Minimum Value" in the lookup, which doesn't exist. Hence the error.

However, I suspect that's just a transcription error, because even if it referenced A3, you'd still have a problem. To understand why you need to look at the definition of LOOKUP(). Specifically:

Note the part where it says:

If search-where is two-dimensional and result-values is specified, the topmost row or leftmost column... is searched..."

Therefore, since your search-where is two dimensional (columns Highs and Lows), it only searches the FIRST column for the search term, which does not exist - in your example, there is no value '3' in the first column of the search-where range.

The solution depends a little on what you're trying to do. If you're trying to find the Time for the lowest value in the Lows column, then simply change the LOOKUP() to only search in the Lows column - there's no need to LOOKUP() in the Highs column, and now you have a 1-dimensional search-where, which will work as you expect.

Edit, Hi again Grampsie,

You found a way with XLOOKUP while I was composing this reply!

Regards,

Ian.

Hi Grampsie99,

Dare I say that your screen shot looks, umm... rather Excel like.

Here is my approach in a Numbers document :

I created that Screen Shot by selecting Table 1 and then a command click on Table 1-1 to include it in the selection.

No need to repeat the column letters in Row 1. The columns in each table are already labelled with their A, B, C ... labels.

Each table in my example has a Header Row that contains the name of that column. Each column with a Header Row in a Numbers table behaves the same way as a named range in Excel.

Using XLOOKUP:

XLOOKUP – Apple Support (AU)

Formula in Table 1-1: B2 is

XLOOKUP(A2,Table 1::C,Table 1::A)

Now for the maximum time:

Formula in Table 1-1 D2 is

XLOOKUP(C2,Table 1::B,Table 1::A)

Please reply if you require further explanation.

Regards,

Ian.

Hi Grampsie99,

Just a thought. Is the 3 in Table 1 formatted as a number or formatted as text?

The same with the 3 in A2 in the table to the right.

Both need to be numbers. Click on a cell and look at the bottom left of the Numbers window to see what Numbers sees.

Another thought. Is the 3 displayed as 3, but is actually not exactly 3?

Yet another thought. Try the HLOOKUP function. It allows a close match or an exact match.

Yet another thought. Does your version of Numbers have the XLOOKUP function? That is more flexible.

Please reply for more help.

Regards,

Ian.

> Here are my new tables, where I think I solved the problem by doing a double "if not found" XLOOKUP

That's fine - as long as all Low values do not exist in the Highs column, or vice versa.

As written, you're looking for the X value, and if that fails you look in the Y, however, it's unclear as to what you should do if the same number appears in both - let's say that 10.0 was the low value at 6:00 and the high value at 3:43... is 10.0 the high or the low value in this table?

That may be OK... I don't know what these numbers represent, or the chance of that happening. Just something to be aware of.