How to find a value in an array in Excel

In order to find the values ​​of a cell located at the intersection of a column and a row in a table array in Microsoft Office Excel, you must use the “INDEX” function, as well as the auxiliary “SEARCH”. Finding a value in the array is required when the user works with a large table, and he needs to “pull up” a series of data. This article will look at a detailed algorithm for using the “INDEX” function to search for values ​​in an array.

Recording the “INDEX” function

Such an array operator is written as follows: =INDEX(array; row number; column number). Instead of words in brackets, the corresponding numbers of cells in the original table are indicated.

Recording the “MATCH” function

This is a helper operator for the first function, which will also be used when looking up values ​​in the array. Its record in Excel looks like this: =MATCH(value to find; table array; match type).

Pay attention! When writing arguments for the INDEX function, the column number is optional.

How to find a value in an array

To understand the topic, the algorithm for performing the task must be considered using a specific example. Let’s make a table of orders in Excel for one day, in which there will be columns: “order number”, “Customer”, “Product”, “Quantity”, “Unit price”, “Amount”. You need to find the value in the array, i.e. create an individual customer order card so that you can get information in a compressed form from the cells of the original table.

To do this, you need to perform a series of actions according to the algorithm:

1. Create a customer order card.

2. For the first line of the card, you need to create a drop-down list in which the names of clients from the main array will be written. Subsequently, by selecting a particular name, the user will see concise information on it, which will be displayed in other lines of the order card.
3. Place the mouse cursor in the first line of the card and enter the “Data” section at the top of the main menu of the program.
4. Click on the “Data Validation” button.
5. In the window that appears, in the “Data type” field, select the “List” option, and select the range of cells of the original array as the source, in which the list of all clients is registered.

6. An arrow will appear on the right side of the cell in the first column of the card. If you click on it, you can see a list of all clients. Here you need to select any client.

7. In the line “order number” write the function «=INDEX(», then click on the “fx” icon next to the Excel formula bar.
8. In the Function Wizard menu that opens, select the array form for the “INDEX” function from the list and click on “OK”.

9. The “Function Arguments” window will open, in which you need to fill in all the lines, indicating the corresponding ranges of cells.

10. First you need to click on the icon opposite the “Array” field and select the entire original plate along with the header.

11. In the field “Line number” you need to fill in the function “MATCH”. In the first place in the parenthesis, as an argument, we indicate the name of the client that was selected in the order card. As the second argument of the “MATCH” function, you need to specify the entire range of customers in the original table array. In place of the third argument, you must write the number 0, because will look for an exact match.

Important! After filling in each element for the “MATCH” function, you will need to press the “F4” button to hang dollar signs in front of each character in the argument. This will allow the formula not to “move out” in the process of execution.

12. In the line “Column number” once again write the auxiliary function “MATCH” with the appropriate arguments.
13. As the first argument for the function, you must specify an empty cell in the “Product” line in the order card. At the same time, it is no longer necessary to hang dollar signs on arguments, because the desired argument should be “floating”.
14. Filling in the second argument of the “MATCH” function, you need to select the header of the source array, and then press the “F4” button to fix the characters.
15. As the last argument, you must write 0, close the bracket and click on “OK” at the bottom of the “Function Arguments” box. In this situation, the number 0 is an exact match.

16. Check result. After doing such lengthy actions, the number corresponding to the selected client should be displayed in the line “order number”.

17. At the last stage, the formula will need to be stretched to all the cells of the order card to the end in order to fill in the remaining lines.

Additional Information! When a client is selected from the drop-down list of the order card, all information on this person will be displayed in the remaining rows of the array.

Conclusion

Thus, to find the desired value in the array in Microsoft Office Excel, the user will have to do a lot of work. As a result, a small data plate should be obtained, which displays compressed information for each parameter from the original array. The method of searching for values ​​with corresponding images has been discussed in detail above.