How do I have a cell to return X,Y,Z if a pop up match a list on another table?

It could be easier for you if you put everything into two tables, one for transactions and one for the category.

In this example, I manually entered data into the Category table, then used INDEX(MATCH()) in Column B of the Transaction table.

=INDEX(Category::C,MATCH(A2,Category::A,0))

Then use Autofill for the rest of the cells in the Transaction table.

[78]

I may have found a creative solution for you.

As noted, this is far easier done if you have the Income, Saving, and Expense entries in a single table.

Here's what my spreadsheet looks like:

My trick (hack?) involves visual manipulation - I created a single table for the Income/Saving/Expense tables, then hid the table gridlines and used individual cell borders to make it look like three separate tables.

Now, since you have a single table, you can use LOOKUP() to search the entire table at once to find the transaction and return the appropriate category.

The formula in Transaction::B2 is:

=LOOKUP(A2,Table 1::$2:$4,Table 1::$1:$1)

This lookup the value in A2 and searches the range Table 1::$2:$4 (rows 2 through 4 in Table 1 - you will need to extend this to cover all the rows in the table if you add more).

For any match, it returns the corresponding cell in row 1 of table 1 (Table 1::$1$1), which gives you the Income/Saving/Expense label.

Fill this down the row and you should be set.

For clarity, here's what the table looks like when a cell is selected. You can see the two 'hidden' columns B and C, but it's all one table as far as Numbers is concerned.

I tend to agree with xtzws.

The main issue is that most MATCH() and LOOKUP()-related functions work across a single table and can't easily compare across multiple tables.

That means you end up with something more complex that needs to first test the Income table, if no match is found check the Saving table, and if still no match found, check the Expenses table. That's a significant overhead.

If merging the tables isn't viable, I'm sure we can come up with something, but it's worth considering.

You can radically simplify your formula, if that's sufficient.

Your current formula is checking the budget category against every individual cell in the Income table using an OR() statement. If that fails it then checks against every cell in the Savings, and so on.

You can replace all the OR() statements with a lookup using MATCH()

Using MATCH() you can search the entire INCOME table for the Budget Item. If it's found you're set, otherwise it returns an error, which triggers another MATCH() on the SAVINGS table.

Here's my Transaction::B2 cell based on my earlier example, with three separate tables for Income, Savings and Expenses:

=IF(ISERROR(MATCH(A2,Source,0)),
 IF(ISERROR(MATCH(A2,Saving::Fund,0)),
 IF(ISERROR(MATCH(A2,Expense::Expenses,0))," ", "EXPENSES"), 
"SAVING"), 
 "INCOME")

It's not dependent on the number of rows in the lookup tables - it will search the entire column, so you can add additional categories at will.

It's still simpler if you have a single table, but I see now that's not practical, so you're still going to have to handle each table separately, but at least this is easier and more flexible.

If you haven't done so already, be sure to have a look at the built-in 'Personal Budget' template at File > New in the menu.

Sometimes it is not necessary to reinvent the wheel!

On the first sheet that template has a 'Summary by Category' table, which uses SUMIF to draw its data from the 'Transactions' table on the second sheet.

You can distinguish between expense and income by making one positive and one negative.

SG