In Excel, the xlookup function is a newly added function, which is very powerful and very useful query reference function, which can help us find a specific value in ** and return the corresponding result.
1. The syntax of the xlookup function
The syntax of the xlookup function is as follows: xlookup(Find Value, Find Array, Return Array, Value Not Found, Match Pattern, Search Pattern) * Find Value: is the value to be found. Required. * Find Array: is the range or array you want to find. Required. *Return Array: is the range or array containing the results to be returned. Required. * Value Not Found: The value returned if a valid match result cannot be found. For example, if we fill in "no result", when there is no value to be found in the queried data, "no result" will be returned. Optional。* Matching mode: 0, -1, 1, 2 can be filled. In general, we fill in 0 to indicate an exact match. Optional。If this parameter is not specified, it will be set to 0 by default. * Search mode: 1, -1, 2, -2 can be filled. 1 specifies the search from the first item to the last item, -1 specifies the search from the last item to the first item, 2 refers to the search in ascending order, and -2 refers to the search in descending order. Optional。If this parameter is not filled, it will be 1 by default. When using the xlookup function, you need to enter the parameters in the above syntax order and separate them with commas.
2. Forward search
Similar to vlookup, look from left to right. For example, there is a **, from left to right is "social security card number", "name" and so on, now you have to query the "name" through the "social security card number", the formula is as follows:
xlookup(j4,a:a,b:b,"No results",0)
Let's fill in "Value Not Found" as"No results", for those that can't be found, it will be displayed"No results"。
3. Reverse search
Since the return array of the xlookup function is different from vlookup, it is independent, so it can be reversed lookup (i.e., from right to left) without restrictions, and the array is needed for reverse lookup compared to vlookup, so the xlookup function reverse lookup is easier to understand and use. Again, in the example above, we query the "social security card number" by "name", and the formula is as follows:
xlookup(j4,b:b,a:a,,0)
If the value is not found, it can be left blank, and the result of the check is empty.
Fourth, the whole line search
One of the powerful features of the xlookup function is that it can query an entire row of data at once, which further improves work efficiency. You can query "Name", "Princes' Country", "Type" and "Total Medical Expenses" through the "Social Security Card Number", and you can query it at one time, and select the return result as B:E, and the formula is as follows:
xlookup(j4,a:a,b:e,,0)
The power of the xlookup function is also reflected in the last parameter, that is, you can specify the search mode, when there are multiple pieces of data, you can fill in the search mode as 1, specify the search from top to bottom, if you fill in -1, you can specify the search from bottom to top, and you can also search according to the data sorting.