Google Spreadsheet Advanced Vlookup and Match Functions

Question:

( By DevinRB )

 I have data in a Google spreadsheet, accessed in Chrome on Windows 8, organized as follows:

Sheet1:

Label     Type
foo       B
bar       A
baz       _(empty cell)
qux       B

totalX    quantity
totalY    quantity

Sheet2:

Type    X      Y
A       2      5
B       1      3
C       3      4

I would like 'quantity' in Sheet1 to sum type-associated values from Sheet2. In this example, totalX would be 4 (1+2+1, or Bx+Ax+Bx), and totalY would be 11(3+5+3, or By+Ay+By).

Conceptually, the formula in totalX could look at each cell in 'Type' column on Sheet1, then if that cell is not blank it finds the value on Sheet2 and adds it to the total. After playing with SUMIF and various formulas, I can't manage to figure out how to do this.

Thanks for the help!

Solution:

Following is the screenshot of 'Sheet2':

Now, to calculate totalX, put the following formula in Cell D2 of 'Sheet1':

=sum(iferror(arrayformula(if(B2:B="";"";iferror(if(match(B2:B;'Sheet2'!A2:A4;0);vlookup(B2:B;'Sheet2'!A1:C4;{2}*sign(row(B2:B));false)))))))

And to calculate totalY, put the following formula in Cell D2 of 'Sheet1':

=sum(iferror(arrayformula(if(B2:B="";"";iferror(if(match(B2:B;'Sheet2'!A2:A4;0);vlookup(B2:B;'Sheet2'!A1:C4;{3}*sign(row(B2:B));false)))))))


Following is the screenshot of 'Sheet1':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive