엑셀 함수 정리 [Office Tip for office workers] Summary of Excel functions ⑤ – Search functions MATCH, INDEX - LG Display Newsroom

엑셀 함수 정리Office workers are fighting a war in a swamp of documents all day long. So, you can easily relate to the phrase above. The LG Display corporate blog introduces office tips for office workers.
Last time, I introduced the VLOOKUP search function. Did I mention that when using the VLOOKUP function, the value to be searched must be in the very first row of the sheet, i.e. row A? However, it is quite cumbersome to edit the table one by one to match the function. For those who are addicted to laziness, I would like to introduce a method to derive the same value by overlapping the INDEX and MATCH functions.

[Office Tip for office workers] Summary of Excel functions ④ – Search function VLOOKUP
If you know your enemy and yourself, you will win every battle! First, let’s look at the concepts of functions one by one.

The MATCH function will be easier to understand if you substitute the concept of coordinates from playing games. When asking for SOS from the same team, you often ask for help by saying, ‘Please come to 2 o’clock!’ Or, more accurately, ‘Please come to (21, 343)!’, which are the coordinates written in the game. That way, your friend on the same side can come to the exact location and rescue you. Similarly, the MATCH function indicates in numbers which column and row the data you want to find is in within the data range.
The INDEX function, on the other hand, pins the value at a specific position in the data range if coordinates are given! It plays a role of finding you. It can be said that they are a complementary relationship, right? Let me explain slowly using the example below.
First, let’s use the MATCH function to find the rank and salary position in the table below. To find coordinate values, the table was separated into row and column positions. Originally, the Excel expression was created by overlapping it directly with the INDEX function expression, but for easier understanding, a separate table was created like this.
At Infinite Trading Company, bonuses are paid differently depending on rank and salary level. For example, Yoo Jae-seok will receive the ‘Manager-No. 4 salary’ bonus. Now, let’s calculate the MATCH value in earnest.
The MATCH function arguments are the corresponding value, data range, and accuracy.
What we need to find is Manager Yoo Jae-seok’s bonus coordinates! First, let’s find the row values. Enter the manager cell value (B4), Manager Yoo Jae-seok’s ‘position’, into the corresponding value of the MATCH function, and then drag the data range ($A$13:$A$17). Lastly, 0 is entered to indicate that the exact value is obtained without error. If you do this, you get 1!

In fact, in the bonus standard table, the manager rank is in row 1. It looks like it was saved well.
People who see this symbol for the first time are bound to be confused! What is it? In Excel, if $ is added to both row and column numbers, it means ‘absolute reference.’ ‘My data range is fixed to this. If it’s ‘It never changes!’, we put a dollar sign on it. Especially in Excel, you often refer to the above formula by dragging the bottom right corner of the cell. In that case, be sure to use $ to ensure that the specified data range does not change! It plays a holding role. You can simply specify an absolute reference by selecting a cell and pressing ‘F4’ on the keyboard.
The method of deriving the column positions is also the same. After entering the MATCH function, click on the 4th peak (C4), scratch the data range corresponding to the peak ($B$13:$E$13), and enter 0… braided! It says 4 rows. In the end, Manager Yoo Jae-seok’s bonus position becomes (1, 4). If you look it up in the standard, it’s exactly right.

Now, let’s calculate the bonus using the MATCH function and INDEX function together. If you clearly understand the MATCH function explained earlier, you can easily find the answer.
INDEX function arguments are data range, row number, and column number. You already learned how to find row numbers and column numbers in the previous MATCH step, right? So, all you have to do is enter the data range carefully.
After entering =INDEX, enter the range ($B$14:$E$18) where the values are written in the bonus standard table. Next, enter the MATCH function (MATCH(B4,$A$14:$A$18,0)) for the row value obtained earlier, and finally enter the MATCH function (MATCH(C4,$B$13:$E$13,0)) for the column value. )), the bonus is 560.
Have you ever noticed? Even if you don’t have a table of row and column positions that you found earlier, you can find the values by overlapping the MATCH function and INDEX function right here! 😀
Lastly, I grabbed the bottom right corner of Manager Yoo Jae-seok’s bonus 560 cell and dragged it, and the bonuses for all members were derived! Don’t forget to add $ to all data ranges!
We went through a lot of processes to find the value of the bonuses for the five members. Some people may say, ‘Wouldn’t it be easier to just look it up in the bonus standard table?’ However, if the number of bonus recipients exceeds 100, 1,000, or 10,000 instead of 5, it will be difficult to handle. That’s why we’ve learned the search function so far. 😀 Then, please look forward to the next office tip for office workers~

[Office Tip for office workers] Summary of Excel functions ① – Basic, COUNT function
[Office Tip for office workers] Summary of Excel functions ② – Logical functions IF, OR, AND
[Office Tip for office workers] Summary of Excel functions ③ – Date and time functions
[Office Tip for office workers] Summary of Excel functions ④ – Search function VLOOKUP

하늘사랑