Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to use vlookup+excel array formula to complete reverse search". In daily operation, I believe many people have doubts about how to use vlookup+excel array formula to complete reverse search. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to use vlookup+excel array formula to complete reverse search". Next, please follow the editor to study!

1. This article explains

Today's question is the interview question of a real data specialist. In fact, there are many methods that can be used to complete this matching search, such as directly using the Lookup () function, or, for example, using the combined function index () + match ().

But the title is explained: use an array to add points. Don't ask me why I use arrays for extra points. Maybe people just think that you won't use arrays, so they will. Haha, don't talk too much nonsense. Let's see how this problem should be done.

two。 The idea of this article

Before we talk about the train of thought, let's send you the final answer of this article. Comrades who will be able to skip this article, students who will not think about it for themselves, and then take a look at my disintegration ideas.

= VLOOKUP ([@ Campus], IF ({1JEO}, BJV BJA A), 2Jing 0)

1) what is an array?

Arrays are represented by {} in Excel, such as {False,9}. Each element in the array can be a logical value, a number, a text, an error value, not a formula, date, or cell reference range. Arrays can be one-dimensional or two-dimensional.

2) the concepts that need to be clear in the array ① commas separate ② semicolon branches

How to understand comma and semicolon branches? In popular terms, comma division means that in an array, if we separate numbers with commas, they end up filling in each column. A semicolon branch means that in an array, if we separate numbers with a semicolon, they end up filling in each line. The text may not be easy to understand, so I have recorded a gif animation to help you understand.

3) to use an array

Perhaps just look at the above gif animation, you do not necessarily know how to operate. Because you don't know the key steps of using arrays, you don't know how to complete the operations in gif animation.

The steps of using ① array

Ⅰ selected filled area

Ⅱ input array formula

Ⅲ key combination: ctrl+ shift + enter

② gives a text description that the filled area is 1 row and 2 columns.

Ⅰ first selects an area with 1 row and 2 columns

Ⅱ enters the array formula. Because it is filled in columns, we use a comma (,) between numbers.

Ⅲ hold down the key combination ctrl+ shift + enter to complete the final filling operation

4) one-dimensional array and two-digit array

One-dimensional array: the direction we fill is only rows or columns, and such an array represents an one-dimensional array.

Two-dimensional array: we fill the direction, including both rows and columns, such an array represents a two-dimensional array

5) the use of multi-dimensional array

Multidimensional array: a multidimensional array refers to the operation between a multidimensional array and a multidimensional array.

For the above table: what should we do if we want to complete the calculation of sales?

① if you do not use an array formula ② if you use an array formula 6) if function in conjunction with the use of arrays

The if () function is mainly used for logical judgment, returning a value in the case of True and another value in the case of False. So what kind of spark does the if () function have when it meets an array?

Simple use of ① if () function ② if () function combined with array completion column filling ③ if () function combined with array completion row filling 7) if () function with array to complete column replacement

As can be seen from the table below, the sales volume is on the left side of the name and the name is on the right side of the sales volume. What should we do if we want to change the order of the two columns?

Do the following:

8) if () array combined with vlookup to complete reverse matching search

As you can see from the figure above: the result of the operation of the if () array is to change the order of the name and sales volume. If the name is on the left and the sales volume is on the right, will we soon be able to use the vlookup () function to find a match?

Do the following:

At this point, the study on "how to use vlookup+excel array formula to complete reverse search" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.