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