2

I'm trying to set up a table column in excel to check for matching names between the name columns of both tables, then return the total from the second table.

Currently this works, but I can't sort or filter either table without causing everything to break.

=IF([@Name]=Levels[@Name], Levels[@Total], "---")

I need a way to make this formula work no matter how the tables are sorted and filtered. The reason for the two tables is that the calculations and separate variables for the levels are too lengthy to insert directly into the roster.

I've tried using the row and indirect functions but I can't figure out how to get them to work.

Variable columns are:

Roster[@Name]
Levels[@Name]
Levels[@Total]
2
  • Can you restructure the data at all? Mar 2, 2015 at 19:55
  • There's too much other data in the roster list to add the level calculations amount to it, which is why I added a new sheet with a new table.
    – Aeon
    Mar 2, 2015 at 19:58

1 Answer 1

0

It looks like you want to take a Name in the Roster table, find it in the Levels table, and return the corresponding Total value. If it's not found, then return - - -. That could be done with your favorite lookup function. Mine is a combination of INDEX and MATCH. It would look something like this:

=IFERROR(INDEX(Levels[Total],MATCH([Name],Levels[Name],0)),"- - -")
5
  • Would this still work if either table were sorted a different way? Such as one table sorted by name a-z and the other z-a.
    – Aeon
    Mar 2, 2015 at 19:59
  • It works so long as there is only a single, exact match for each name. The order doesn't matter. If you a name appears multiple times, this only pulls the first instance. If you want to sum the value for all instances of the name, we can do that, too. Mar 2, 2015 at 20:08
  • Single match names is all I need ^^ thanks. I'll test this out and make sure nothing breaks since the workbook is very function heavy.
    – Aeon
    Mar 2, 2015 at 20:10
  • Works perfectly with no conflicts! Thanks again for the help!
    – Aeon
    Mar 2, 2015 at 20:11
  • Excellent. Glad to help. INDEX+MATCH is powerful and versatile. Mar 2, 2015 at 20:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .