( by Courtney Whitaker )
I am trying to create the following formula in cell D2: If C2 = "Company" then lookup range of Company!A:B then match Data! column A to Company! column A and display the value from Company! column B.
If C2 = "Owner" then lookup range of Owner!A:B then match Data! column A to Owner! column A and display the value from Owner! column B.
Data:
A B C D
Employee # Name Type Vehicle #
1 AB123 Andrew Baker Owner *FORMULA HERE*
2 CD456 Chris Davidson Company *FORMULA HERE*
3 EF789 Eric Ferguson Company *FORMULA HERE*
4 GH123 Gary Harper Owner *FORMULA HERE*
Company:
A B
Employee # Vehicle #
1 CD456 1111
2 EF789 2222
Owner:
A B
Employee # Vehicle #
1 AB123 0000
2 GH123 3333
Now, instead of having formulas in each and every cell, you can optimize the performance of your spreadsheet by using ArrayFormulas and by reducing these excessive formulas.
Have a look at the following screenshots of sheets my spreadsheet:
Sheet "Company":
Sheet "Owner":
Sheet "Main":
I have the following formula in Cell D2 of Sheet "Main":
=arrayformula(if(C2:C="";"";if(C2:C="Company";vlookup(A2:A;'Company'!A:B;{2}*sign(row(A2:A));false);vlookup(A2:A;'Owner'!A:B;{2}*sign(row(A2:A));false))))
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:
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
No comments:
Post a Comment