How can I specify all columns of a table in the HLOOKUP function?

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).

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

XLOOKUP – Apple Support (AU)

Regards,

Ian.