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.
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.
Again, awesome. Easy, editable, and tons of possibilities.
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?
Dear Tom, can we apply this technique in all cases / formulae and avoid CSE altogether?
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.
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)
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.