I'm running Numbers on an iMac. I specify the range for the HLOOKUP as Table 1::$C$2:AL$32. Is there a way to specify all columns of a table instead so that I can copy the formula and use it in another sheet where the number of columns might be different?
iMac 21.5″, macOS 12.7
Hi Betty,
Try this:
Formula in Table 1-1 B2 is HLOOKUP(A2,Table 1::$1:$2,2,0)
Or, with the superior XLOOKUP (to remove those red error triangles):
Formula in Table 1-1 B2 is XLOOKUP(A2,Table 1::$1:$1,Table 1::$2:$2,"Not Found",0)
More here on XLOOKUP
Regards,
Ian.
You can specify all the columns using "whole row" range reference (example is for rows 1 through 22 of Table 1):
Table 1::1:22
This includes columns A and B, neither of which are in the references you are currently using.
Note that if your other tables have more rows, it will not include them all. If your other table has fewer rows, it will be a reference error.
Also, if you add more rows to the bottom of the table, it will not automatically include them (same as with the ranges you are currently using). However, if you add rows in the middle of the table it will include them or if your last row is a footer row and is included in the range it will include new rows added at the bottom (because they get inserted above the footer row).
How can I specify all columns of a table in the HLOOKUP function?
