Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to deploy Array Constants with VLOOKUP to handle the table array argument. Let’s get started
Tutorial Video
You can watch the tutorial video below
Sample Data
We have a sample data below showing transaction records.
VLOOKUP Without Array Constants
Based on the caption above, we want to return the commission in the table to the right based on the specified look up values.
Therefore:
- In cell P4, execute the formula: =VLOOKUP(O4,$S$4:$T$8,2)
where cell O4 is the lookup value.
$S$4:$T$8 is the absolute reference for table array
2 is the value that handles the column index number.
The VLOOKUP, by default, performs approximate match hence, the fourth argument was skipped in the formula.
- Click Enter and copy down the formula using autofill
In the caption below, we can see that the returned commission for Sales Amount $17,739.98 in cell O4 is $450.
VLOOKUP with Array Constants
We have seen how to traditionally perform approximate match VLOOKUP. Next, we want to see how to use Array Constants inside the VLOOKUP Function. To use the array constants, we must deploy curly braces and commit the formula using legacy CTRL + SHIFT + Enter on the keyboard.
- In cell Q4, execute the formula: =VLOOKUP(O4,{0,0;15000,450;40000,1200;60000,1800;80000,2400},2)
where O4 is the lookup value.
0, 15000,40000,60000,80000 and 0,450,1200,1800,2400 are the array constant serving as input value for table array argument.
2 represent the column index number which is the column to return.
- Click CTRL + SHIFT + Enter and copy down the formula. Note, I used Excel for Microsoft 365 E5 hence, I do not need to apply the legacy CSE.
In the caption below, we can see that we achieved the same result with the values in column P without referencing any table array!
Conclusion
We have seen it is possible to perform VLOOKUP formula with Array Constants. If you enjoy this tutorial, do watch the video, subscribe to the YouTube channel. See you in the next tutorial