Dear friends, let me share my story with you. As a married working woman, I have spent nearly 20 years in my career. Frankly, I'm tired of the internal friction in the workplace, and I want to live beyond the constraints of work and feel the wider world. Therefore, I decided to start today to share with you the excel skills that I have mastered, which have accompanied me through countless overtime hours, made our work more efficient, and gave us more time to embrace life and savor those beautiful moments.
About five years ago, I embarked on the job search to get a position as an administrative manager at a listed company. I couldn't help but be a little nervous when faced with the HR manager's question. He asked me, "Please explain to me what the vlookup function in excel means and how to use it." ”
I know that the vlookup function is very useful for conditional queries, but its limitations are also very obvious. In the past, when I was not familiar with Excel, I might have regarded this function as a treasure, and I could even blurt it out, as if it could add points to my professional ability. However, after years of study and practice, I can responsibly say that the vlookup function has almost disappeared from my actual use. Right now, only fuzzy search is still in use (I'll show you how to use it in a later article).
Back to today's topic, I really don't use the vlookup function in my daily work, I use the index+match function instead, let's get to know these two functions.
index(array, row_num, [column_num])
The first parameter: array
The second parameter: row num
The third parameter: the column num
As we all know, the structure of excel is like a coordinate system, crisscrossing and forming cells. The index function, on the other hand, acts like a savvy navigator to determine the position of content through the intersection of rows and columns. For an in-depth exploration of Excel, we need to understand that the identification of cell content depends on the intersection of rows and columns. It's like our planet, with latitude and longitude, we can pinpoint every location. The index function undoubtedly has such a precise positioning characteristic in the Excel function family.
match(lookup_value, lookup_array, [match_type])
The first parameter: what to look for (lookup value).
The second parameter: lookup array
The third parameter: the column or row of the result ([match type]).
The match function is very similar to the vlookup function, but the difference is that the final result returned by the match function is the number of rows or columns.
So let's take a look at the use of the index+match function:
Search salesman according to sales volume =index(c1:c12,match(h2,d1:d12,0)).
Understand that the match function is used to find out the rows of the sales volume 1785 in the sales column (the calculation result is the 10th row), and then calculate through the index function that the 10th row of the salesman's column data must be Aisha.
Dear friends, today's sharing has come to an end. But remember, the mysteries of Excel functions are still up for yourselves to explore. The path to learning is not rote memorization, but a deep understanding of how it works. When you grasp the principle, all problems will be solved. May you go further and further on the road of learning, and see you next time!
New Year's Powder Bootcamp