Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

When using a lookup function such as VLOOKUP, you can avoid a lookup table on a worksheet and insert the lookup elements directly into the formula itself. This is done by using an array constant which is shown by example in the pictured formula
=VLOOKUP(B4,{0,"D";71,"C-";72,"C";76,"C+";79,"B-";
82,"B";86,"B+";89,"A-";92,"A";96,"A+"},2)
.

I posted this formula on two lines so it can be read by all web browsers on this blog page. In reality, you enter the formula on one single line in the cell as you would with any formula.

Formulas that contain array constants do not need to be applied with Ctrl+Shift+Enter as you need to do with regular array formulas. This is because you have already built in the array elements manually, such as the formula shows, with horizontal elements separated by a comma and vertical sets of elements separated by a semicolon. Note that with array constant formulas, you do enter the curly braces { and } yourself, which enclose the entire group of array constant elements.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table
  1. Rajat Agarwal says:

    That is exactly I use instead of building a large if condition . I used to build the table which is easy to maintain but does not look good on a final file. Thanks for this tip, would be helpful.

  2. Jay says:

    Again, awesome. Easy, editable, and tons of possibilities.

  3. Jay says:

    Do you have more references to working with this kind of data? Wondering what the rules are for what you put in that array and the rules around it. Does the function always reference the numbers in the order you placed them?

  4. Sandeep Kothari says:

    Dear Tom, can we apply this technique in all cases / formulae and avoid CSE altogether?

    • Tom Urtis says:

      VLOOKUP does not require an array construction. There will always be arrays in Excel for which similar workarounds of the type you suggest would be impractical.So in effect, the short answer to your question is no.

  5. shree says:

    This is super amazing, almost what I was looking for! I have an issue with the matching though, is it not possible to lookup for text and bring back a value? This formula doesn’t work..
    =VLOOKUP(B4,{“A”,”X”;”B”,”Y”;”C”,”Z”},2)

    • Tom Urtis says:

      The reason it doesn’t work is that there is no lookup result specified. You show 2 at the end of the formula that would be the index column where the value result would exist. But in the formula there is only 1 vector. If you can give an example of what is in cell B4, and what would determine what value should result. I honestly don’t understand if A, X, B etc are supposed to represent column letters or return values.

1 Pings/Trackbacks for "Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table"
  1. […] the look array inside of the formula using an array constant. You can read Tom’s explanation here. The important thing to remember is that Tom is binning the numbers and the answers are the letter […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*