Numbers conditional formatting help

From your description it sounds as if you are trying to do a lookup. You could try something like this:

The formula in D2, filled or copied down, is:

=XLOOKUP(B2,Table 2::A,Table 2::B,"not found")

Or, if your region uses , as a decimal separator:

=XLOOKUP(B2,Table 2::A;Table 2::B;"not found")

SG

HI James,

'if I want the lookup formula to search column A of Table 2 and return the contents of Column B just as it has in your formula, but I also want it to Search column A of Table 3 and return the corresponding number from column B if the first lookup of Table 2 returned nothing'

(Initial section deleted, as it contained notes on a version of the formula below that wasn't working.

Working formula is displayed and discussed below.) B.

Here are screenshots of the lookup tables on Sheets 2 and 3.

Sheet 2:

Sheet 1:: Table 1:

The formula shown below the four search results is entered in the selected cell (B2), and filled right to D2, then filled down to the last row in the table.

In rows where there is no name entered in column A, the formula will match the first empty cell it finds, and return the data in columns B, C and D of the row in which that empty cell was found. If these cells are also empty, the formula will return zeros to the cells on this table.

Note that there is no text entered in the If not found token. This omission makes XLOOKUP throw an error message, which is caught by IFERROR. IFERROR then runs the second XLOOKUP function, which searches the same name in the lookup table on Sheet 3.

If this search also fails to find the name, XLOOKUP calls the I fnot found argument, and the 'not found" text there is returned to the cell containing the formula.

Regards,

Barry

PS: An alternate route to running an XLOOKUP search on more than one lookup table might be to use UNION RANGES to combine the search ranges and the return ranges from two or more tables into a single search field.

James.kerski wrote:

what if I have a third sheet that also has a column of names and corresponding numbers in the next column

When things start getting complicated like this it can be helpful to consider whether there are ways to simplify the structure of document. For example, do you really need two separate tables, each with a column of names and and a column of corresponding numbers?

It is often much more efficient to keep "like" data (data in similar form) all together in the the same table.

Maybe one table had a names and numbers for month 1, and the other table had similar data for month 2. Then put all the dat in one table and add a column for month.

It may seem repetitive to list month 1 month 1 month 1 month 2 month 2 month 1, etc. down the third column. But doing it that way, rather than having separate tables, will make it much easier to look up, extract, or summarize information.

SG