Creating a table array argument allows you to organize your data into a structured format. That said, it becomes easier to retrieve information in Excel.
Around 90% of Excel beginners don’t know what a table_array is and how to create one.
Are you one of them?
Keep Reading, As we are about to guide you how to create a table_array argument in Excel the actual way.
But before that, let’s know-
What is a Table_Array Argument in Excel?
The table_array argument is the range of cells that contains the data to be searched, and it typically includes the lookup value and the result value.
You might have heard the names of VLOOKUP and HLOOKUP functions. We used VLOOKUP to return all matches, remember?
The array table argument is the required input for these two functions.
The lookup value is the value you are searching for, while the result value is the value you want to return if the lookup value is found.
While dealing with LOOKUP Values, you have to ensure that the table is properly structured and contains all the necessary data.
Apart from the table array feature, we have also used the term argument.
Now, what is an argument?
“Argument” refers to a value, cell reference, range, or text string that is passed to a function or formula as input.
There are several Excel functions.
These functions are pre-built formulas that perform specific calculations or actions on a range of data. When you use a function in Excel, like the text and format functions, you typically need to provide one or more arguments to tell the function what data to work with.
How To Create a Table Array in Excel
You can either create a generalized table or you can highlight the range of cells required to create the array.
To create a table array, do the followings:
- Open an existing or a new Excel spreadsheet.
- Add necessary data to the cells.
- Select the cells including the header (if any) and click on the Insert tab.
- Select the Table option and Excel will automatically generate a table.
Note: If your table doesn’t have headers, uncheck the “My Table has headers” option.
You can change the Table name along with the Table designs if you wish.
The next thing you need to do is open up the array functions and type the formula.
Refer the whole cell as table_array. You can either do it by selecting the header and pressing Ctrl+Shift+Down Arrow Key. Or you can use your mouse to select the range of cells. Additionally, you can use the designated Table name as well.
What are Array Formulas?
Array formulas, also known as CSE (Control+Shift+Enter) formulas, are a type of formula in Excel that allow you to perform complex calculations and manipulate large sets of data.
These types of formulas are usually surrounded by curly brackets {}.
Array formula follows the same syntax principle, same mathematical operation, and same operational order.
There are two types of arrays.
- One-dimensional Array
- Multi-dimensional array
Difference between one dimensional and Multi-dimensional array includes-
| Criteria | One-Dimensional Arrays | Multi-Dimensional Arrays |
| Definition | A formula that returns a single result or array of results from a single row or column of data | A formula that returns a single result or array of results from multiple rows and/or columns of data |
| Usage | One-dimensional array formulas are used when you want to perform calculations on a single row or column of data. | Multidimensional array formulas are used when you want to perform calculations on multiple rows or columns of data. |
| Complexity | One-dimensional array formulas are typically less complex than multidimensional array formulas, and easier to understand and troubleshoot. | Multidimensional array formulas can be more complex and harder to understand than one-dimensional array formulas, and may require more time and effort to troubleshoot. |
Common Syntax: Must be enclosed in curly braces { } and entered as an array formula with Ctrl + Shift + Enter.
Example Formula: {=SUM(A1:A5*B1:B5)}
Example Result: Returns a single result, the sum of the products of each cell in A1:A5 and B1:B5.
How To Use the Table Array Argument in a VLOOKUP Function
You can use the VLOOKUP formula to compare two columns in different sheets. But for that, you do have to use the array argument. For that there are four parameters.
How?
Here are the steps to use the table array argument in Excel:
- Arrange your data to vertical columns to use the VLOOKUP value.
- Enter the VLOOKUP command in a separate cell.Type =VLOOKUP(F4, …). The first argument determines the value we are looking for. It can be a value, text, or even the cell number.
- Select the table array. In other words, the range of cells to look for the value. Type =VLOOKUP(F4, $B$2:$C$7, …). The second argument determines the table_array parameter. You can even set a name for the table.
- Choose the column number from which you want Excel to look for the value. Type =VLOOKUP(F4, $B$2:$C$7, 2, …). The third argument deals with the column number. If you chose the table range from A to B. That means, A is column 1 and B is column 2. If you would have chosen the range from A to C and want to look for the corresponding value from the column C then you would have to type the number 3.
- Determine whether you want the approximate match or not. So, the fourth argument is an optional one. Type =VLOOKUP(F4, $B$2:$C$7, 2, FALSE). TRUE refers to approximate match, FALSE refers to exact match. If the value is omitted, Excel by default considers the fourth argument as TRUE.
Lock the columns, otherwise you will face some errors (mentioned in the next section).
Most Recommended Content: How to make Excel Tables Look Good.
#REF Error in VLOOKUP Excel?
There are three types of error you will be getting while using the VLOOKUP function. #N/A, #REF, Value 0 (zero) error.
Meaning of the Errors and How to Troubleshoot Them
#N/A – The not available error occurs because the value you are looking for is not present in the Table array or range of cells you are referring to.
Solution: Cross-check the value you are looking for.
#REF – This error is the most common one. The reference error occurs because the value you are looking for is not present in the Reference cell.
Solution: Open the Formula Editing mode by pressing F2 and check whether the reference cells are accurate and made absolute (Select the range of cells and Press F4).
Value 0 (zero) – You may see a zero value even if the VLOOKUP formula is typed accurately. This occurs because the value you are looking for in the corresponding cell is blank.
Solution: Replace the blank cell with a value.
FAQ
Q: How to name a table in Excel?
A: If you already have a table generated by Excel, then click on the table. Navigate to the Ribbon bar. You will see an option named Table. Click on the Table tab and at the leftmost side, you will see an option called Table name. Rename the table as per your requirements.
Q: How to find the array of a table in Excel?
A: You can find the array table by utilizing the Go To Special command in Excel. Press the Ctrl+G buttons on your keyboard. Select My Table under the Go To Dialog box. Select OK.
Conclusion
Now that you know the exact meaning of Array formulas, arguments, and how to create a table array argument in Excel, you should now be able to use any dynamic array formulas with it.
One thing to remember:
Do lock the table_array using F4 on your keyboard. Otherwise, Excel will return some unexpected values without even showing the errors.
