## Friday, July 19, 2013

Question:

( 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

Solution:

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

Thanks,