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: , , , , , , , , , , ,
5 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.

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 to Jay Cancel reply

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

*