# Replace multiple SumProduct/Vlookup with a dynamic array of conditions

1. ## Replace multiple SumProduct/Vlookup with a dynamic array of conditions

Hi guys,

I have tried to see if there was a similar post to my issue but are not able to find something that match what I am trying to do.

I have been assigned to come up with a roster and I'd love to help the one from someone that feels comfortable with it. I am working for an NGO so resources are quite limited, thus me an HR person doing the roster for medical people Anyway I'd like to minimize a formula I am using to calculate the total number of shift in my roster for every person that work.
I am using a mix of SumProduct and Vlookup in the formula to calculate the total. The idea behind it is to make it as simple as possible, which is far to be the case now.

Below my two tables:

TABLE 1 > A1:F3
 Total Bob D1 D2 D2 D2 7 Kyle D3 D3 D1 D1 8

TABLE 2 > A5:B7
 D1 1 D2 2 D3 3

Formula in F7:
=SUMPRODUCT(--(B2:E2="D1")*VLOOKUP("D1",\$A\$5:\$B\$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D2")
*VLOOKUP("D2",\$A\$5:\$B\$7,2,FALSE))+SUMPRODUCT(--(B2:E2="D3")*VLOOKUP("D3",\$A\$5:\$B\$7,2,FALSE))

Problem
As you can see the formula is already quite long and I must admit pretty messy. Problem is if my table becomes 15 rows I'll have to add everything manually.

Would it be possible to have all conditions dynamic so that I don't need to enter any condition manually as it is currently the case, e.g. "D1", "D2", "D3"?

Not sure if that makes sense to any of you but would definitely appreciate some wisdom.

Thanks a lot,
Fab  Register To Reply

2. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

Try putting the formula below in F2 using Ctrl + Shift + Enter to confirm it. Fill down as necessary.

=SUM(LOOKUP(\$B2:\$E2,\$A\$5:\$A\$7,\$B\$5:\$B\$7))

Edit: At some point, you'll want to get table 2 out of table 1's way, I imagine. Just change the A5:A7 and B5:B7 portions of the formula to conform to wherever you relocate table 2. Beyond that, you shouldn't have to make adjustments for every new entry in table 1.  Register To Reply

3. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

I agree with cantosh. Get Table 2 out of the way. This is what I did. I moved it to Sheet 2

I made it an Excel Table with the name Table_Lookup. There are several advantages to using tables. One of them is that they use the table name in the column headers instead of cell references, so the formula becomes: =Vlookup(A2,Table_Lookup,2,false) instead of =Vlookup(A2,Sheet2!\$A\$2:\$B\$4). Another advantage is the table knows how big it is so you don't have to guess at the range or change the formula when you add data. If you add a new D value to Cell A5 on sheet 2, it becomes part of the table.

This makes the formula cantosh provided =SUM(LOOKUP(B2:E2,Table_Lookup)).

I also put the data on Sheet 1 into an Excel table. Another advantage is that when you add a row to a table, the formulas get copied down automatically.

Finally, just for fun, I put data validation on the cells to look up only valid data. =INDIRECT("Table_Lookup[Value]") Oh yeah, that's yet another advantage of Excel Tables, they are automatic dynamic ranges that can be used for lookup. And yes, when you add a row to a table, the validations get copied down too.  Register To Reply

4. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

@Cantosh @dflak

Wow you guys rock Thanks a lot that is exactly what I was looking for. I used both of your technics and it works like a charm.
I am going to put all generic table in one sheet and make good use of the Table into Excel (simple thing I was not even aware of).
Just assumed since I was in Excel I was already creating a table.

Have a great weekend,
Fab

PS: Array formula + Tables = Happy Man!  Register To Reply

5. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions Originally Posted by dflak There are several advantages to using tables. One of them is that they use the table name in the column headers instead of cell references, so the formula becomes: =Vlookup(A2,Table_Lookup,2,false) instead of =Vlookup(A2,Sheet2!\$A\$2:\$B\$4).
Not everyone would agree that structured table nomenclature is a good thing. If I just have you the formula =Vlookup(A2,Table_Lookup,2,false) with no table to reference, you would probably have a hard time picturing what it was doing, but a formula like =Vlookup(A2,Sheet2!\$A\$2:\$B\$4) is easy to read, and you can immediately interpret what it is doing.

I, for 1, am not a big fan of how formulas are represented from structured tables.

edit: dont get me wrong, they do have some serious advantages of a range, they are a tool, just like any other function, and have their place in any tool box   Register To Reply

6. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

We'll just agree to disagree on this point.   Register To Reply

7. ## Re: Replace multiple SumProduct/Vlookup with a dynamic array of conditions

Not agreeing or disagreeing, just stating a different opinion   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 