Friday, July 19, 2013

Google Spreadsheet Conditional vlookup in ArrayFormula


( 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. 

       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*

  A             B
    Employee #       Vehicle #
1 CD456 1111
2 EF789 2222

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":


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: 


No comments:

Post a Comment