CODEWITHSUNDEEP
excelvba
114
114

Reputation: 926

Stringing Together VLOOKUPs

I have the following formula

=SUM(IF(A1<>"",VLOOKUP(J3,M3:N7,2,FALSE),0),IF(...),IF(...),...)

which effectively sums up the values in a VLOOKUP table associated with words in particular cells (e.g. A1). The goal is to get a sum of values for strings containing a variable number of words, where each word has a certain value. For example, one string might contain 4 words while another might contain 22. I would like to figure out a way to do this in VBA so that instead of just writing enough IF() statements to account for the maximum number of words any given string in my set of strings Excel knows to simply count until reaching an empty cell, sum the values of all the VLOOKUPs conducted on non-empty cells, and stop. Would anyone be able to guide me through how to do this?

Upvotes: 0

Views: 363

Answers (1)

John Chrysostom
John Chrysostom

Reputation: 4063

Instead of using lots of vlookups, why not use a single array formula with sumif?

I created the following test spreadsheet:

    A    B    C   D
1  xz    zt   bg
2
3  bg    12
4  xz    24
5  zt    55

To lookup the values of A-C in the table below and sum their values, I used a formula like this in column D: =SUM(SUMIF($A$3:$A$5,A1:C1,$B$3:$B$5)). Make sure to ctrl+shift+enter so it's an array formula, and boom! you're done.

This will work perfectly, even when a variable number of cells in columns A-C (or however many you might have) contain data... The only caveat is that it assumes that each word has only ONE entry in the lookup table, but I figured that was a safe assumption. Long story short, there's no need to get into VBA for this.

Upvotes: 4

Related Questions