Hello everyone. Oppadu Excel is back! In this lesson, we will look at XLOOKUP function, a new function of Excel. It is similar to the existing VLOOKUP function. It is simpler and more powerful. I'll look at the screen together. So type XLOOKUP. After selecting the value to find, select the range to find and select the output range. If you change the name like this, you can see the employee number changes. The existing VLOOKUP function could not be found on the left side, but the XLOOKUP function can look up values regardless of left or right. If you are using Office 365, please see the end of this lesson. Are you ready? Let's go together! It's an essential function for office workers, the VLOOKUP function. If you are an office worker, I think it might be the most used function of the day. Finally, we update the XLOOKUP function to replace the VLOOKUP function in Excel. Please refer to the summary table on the screen for the difference between VLOOKUP and XLOOKUP. Even if you are using Office 365, you will not see the XLOOKUP function as of September 27th. I need to apply for office. To apply, go to [File]-[Account]. There is a button to apply for [Office Participant]. For how to apply for the office participant, we have written the related link at the bottom of the video. Let's take a look at the lack of the existing VLOOKUP function and the improvement of the XLOOKUP function one by one. In the VLOOKUP function, the range to find the value to look for must be far left in the reference range. The department in charge is printed with reference to the employee number. Since the range to be printed is to the left of the range to be searched, VLOOKUP did not look up. So I had to use the INDEX / MATCH function. Select the value you want to find with the VLOOKUP function and then select the reference range. Since it's on the left side, put -1, zero as the last argument, and the function ends. I get an error The XLOOKUP function can refer to values regardless of the range position they are looking for. Type XLOOKUP, select the value you want to find, select the range to find and the output range, and press enter. Very simply, the department in charge according to the employee number is inquired. If you change the staff number, you can see the department in charge change accordingly. The VLOOKUP function changed the output when adding a column to the reference range. The D: F column has been entered as a reference range. It is returning age, the third column of this range. If you add a column in the middle, the output changes to zero. As the number of columns is added, the reference range is increased, but the third column is displayed while the number of columns to be printed is maintained. If you clear the column, you will get a #REF error. The XLOOKUP function retains the output even if you add a column to the reference range. With XLOOKUP, select the name you are looking for. Select 'Name Range' which is the range to find and 'Age Range' as the output range. Adding a column retains the value. Similarly, deleting a column causes the VLOOKUP function to fail while XLOOKUP retains the value. Next, the VLOOKUP function has an optional argument called 'match option' as the last argument. The problem was that when I actually used VLOOKUP, I searched for an exact match, not similarity. It was common to use a # N / A error if there was no value found. Therefore, when using the VLOOKUP function, I made a function by putting 0 at the end. If you do not put 0, the unknown value is displayed even though there is no employee named 'Sine.' When you entered the VLOOKUP function, you had to put zero or FALSE at the end every time to find an exact match. So we had to put four as arguments every time. The XLOOKUP function works just fine with three arguments, because the match option defaults to exact match. Enter XLOOKUP and select the name you want to find. Select the range to find and output range and enter it. There is no # N / A error because the employee named "Sine" does not exist. If you change it to 'Sine-Shu', you can see the value coming out. The VLOOKUP function can only return the top value if there is an identical value in the search range. There are two employees named Park Na-yun, one on the IT team and one on the customer care team. If you look up the age of an employee with the VLOOKUP function, you can only return the highest value. In the previous lesson, I explained how to return the last value using the LOOKUP function. It's much simpler if it's handled as a function rather than two functions. The XLOOKUP function can select and return the top or bottom value even if the same value exists in the lookup range. Type XLOOKUP and select a name. After selecting the name range and output range, enter two commas. Ascending search, descending search search options appear. Select as descending search here. Entering the function returns 33, the age of the last employee, Park Na Yoon. Changing the search option from descending to ascending order returns 32, the value at the top. The VLOOKUP function involved unnecessary computation when referencing a wide range. The age is displayed by referring to the employee number. As a result, we had to reference all ranges from column C to column F. In other words, even though the columns in the middle of columns D and E are not needed, the range is added into the function, which adds unnecessary calculations. The XLOOKUP function, on the other hand, selects and enters only the required range, so it can operate faster. Next, the VLOOKUP function cannot look up the horizontal range. To look up a horizontal range, we used the HLOOKUP function or the VLOOKUP / MATCH function. Use the HLOOKUP function to select the value to look for in March, then select the range and add 2 to output the value located in the second row, and end with 0 as the last argument. It would be cumbersome to use 2 functions each time for horizontal and vertical ranges. The XLOOKUP function can look up both vertically and horizontally. Enter XLOOKUP, select April as the value to find, and select the range and output range to find. In April, 1,200 won, and March, 1,350 won, you can view up to the horizontal range. The XLOOKUP function can also be searched by wildcard. Wildcards are any characters that can substitute for any character. Asterisks and question marks are used. The asterisk replaces any character, regardless of the number of characters. The question mark replaces only one character. If you put a question mark (?) After Kim, the name of the two letters starting with Kim will be searched. The two letters starting with 'Kim' are only 'Kim Joon', so if you search with XLOOKUP function, OPD013 will be printed as employee number. Select the name that you want to find with the XLOOKUP function. Select 'Name Range' to find and 'Employee Number' to display. If you enter a comma one more time, you'll see a wildcard match in Match Options. OPD013 is returned to the employee number of Kim Joon, an employee with a two-letter name beginning with Kim. If you add another question mark afterwards, OPD016 will be returned to the employee number of Kim Min-chae, a three-character employee starting with Kim. The XLOOKUP function is a powerful function that can replace several functions, not only the VLOOKUP function but also the HLOOKUP, INDEX / MATCH function, and LOOKUP function, while supplementing the problems and adding features of the existing VLOOKUP function. Nevertheless, there are some drawbacks. Firstly, the XLOOKUP function returns a #VALUE error if the range of the search and the output range are different. This is the same problem as SUMIF and COUNTIF which are commonly used. There are two ranges, but if the ranges are different lengths, you will get a VALUE error. Secondly, Excel beginners who select a range with the mouse can take a long time to enter a formula. The VLOOKUP function selects a value and then has only one range to select. The XLOOKUP function requires you to select two values, two ranges. But the problem is, if you want to select a long range multiple times, it will take a long time to select it by dragging it with the mouse. In this case, you can use keyboard shortcuts to select with Ctrl + Shift + arrow keys. If you make it a shortcut, the second problem can be easily solved. Advanced features such as multiple conditions, image output, and multiple result output should use the INDEX / MATCH function. Fourth, I think this is the biggest problem. XLOOKUP function is provided only in Excel version of Office 365. As a result, an error is outputted when sharing files with all previous users, including Excel 2019 installed. So this is not a problem when you first open the file, but if you change the value or run the function again, it will return a #NAME error. Compatibility issues arise when sharing files with multiple users. It's my personal wish, but not only for Office 365 subscribers, but also for Excel 2013 and beyond, I have a personal wish that many users will be able to use the more convenient features by providing the XLOOKUP function. In this lesson, we learned more about the new XLOOKUP function in Excel. Later, when the XLOOKUP function is distributed to general users, we will prepare the lesson with more practical information. If you have any questions during this lesson, please leave a comment below. If you enjoyed this lesson please subscribe and subscribe.
