How To Use VLOOKUP With An Array Constant In Excel

Here are some interesting ways you can use Array Constants in your VLOOKUP formula!

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

7 Responses to How To Use VLOOKUP With An Array Constant In Excel

  1. Penny March 10, 2015 at 10:57 am #

    I love this tip! Still perplexed on how the F9 changes the array to a constant group. Acceptance is the answer, I guess I don’t need to figure it out. Just use it:)

    • Michael Rempel March 10, 2015 at 11:13 am #

      Penny, if you highlight any formula or portion of a formula in the formula bar and hit F9, it will show the results of that formula. To go back to the formula, hit ESC, but if instead you just hit enter, the results will remain instead of that portion of the formula. Same with an array constant. highlighting that portion of the formula and hitting F9 reveals the result of that portion of the formula – i.e. the array constant. Hope this helps. Try it with any formula in a workbook you have.

  2. MAJ May 11, 2015 at 8:54 am #

    Michael, i still need a favour. After hitting enter, how can i get back the previous portion of formula?

    • Michael Rempel May 11, 2015 at 9:18 am #

      MAJ, if immediately after you converted it to an array formula, just use your undo command, or Ctrl + Z. If later on and you still maintained your table array, you can highlight the array constant in your formula, including the curly brackets, delete those and highlight the table array to insert that back. Does that answer your question?

      • MAJ May 12, 2015 at 4:36 am #

        Thanks Michael for the response.
        Assume that i have changed my formula to constant; and closed the file after saving. The table array is not yet deleted.

        Is there any way to get the formula back to previous version, i.e. formula with table array?

        • Michael Rempel May 12, 2015 at 7:19 am #

          MAJ, I’m not aware of any programming way to get the formula back to the previous version other than what I indicated in my comment – assuming the data range that was converted to the table array still exists, highlight and copy that range, the delete the table array from the formula and paste in the original data range. Sorry I can’t provide any other alternatives.

          • MAJ May 12, 2015 at 7:41 am #

            Hi Michael,
            This alternative is fine in case we know the source of array constant. We may not remember the source of data (formula) if file is opened after a good interval.

Leave a Reply