In this post, I am going to discuss the XLOOKUP formula available in Excel.
XLOOKUP is a brilliant formula that I use absolutely loads. It helps you search for information relating to other data within a table in Excel. Rather than you having to scroll through loads and loads of information by hand, you can spend five minutes writing this formula and let it do the work for you.In this example we are going to use the example of searching for an athletes current one rep max for an exercise.
Understanding the Formula
Understanding each section of the formula, allows you to quickly identify what information you need to put where.
lookup_value: This is what we are going to use as our base piece of information. In this case, it is our athlete's name.
lookup_array: This tells Excel where we want to search for the athlete's name. In this case, it is the column of our table that contains the athlete's name.
return_array: Once Excel has found the athlete's name, we need to tell it what information to return. In our case, it is the column that contains all the 1RM information.
[if_not_found]: If Excel cannot find a valid match, we need to provide something for it to return. I generally use "No Data".
[match_mode]: How much of a close match do we want Excel to search for? This section of the formula is optional.
0 - Exact match. If no match is found, return the text you specified earlier.
-1 - Exact match. If no match is found, return the next smaller item.
1 - Exact match. If no match is found, return the next larger item.
[search_mode]: This tells Excel in which order we want to search our data. it is also optional.
1 - Search first to last.
-1 - Search last to first.
2 - Binary search that relies on your data being sorted in ascending order. If not sorted, invalid results returned.
-2 - Binary search that relies on your data being sorted in descending order. If not sorted, invalid results are returned.
Seeing It In Action
lookup_value: F3, the athlete name
lookup_array: C3:C14, rhe range containing our athlete names
return_array: OD3:D14, the range with all E1RM information
[if_not_found]: Return "No Data" if no exact match.
[match_mode]: 0, return the above message if no exact match
[search_mode]: Search first to last
You can use =XLOOKUP for so many things relating to athletes: dates, sessions, exercises, and so much more. If you want to know more about using XLOOKUP, I use it extensively in the Athlete Lift Log Book Tutorial available here.
Comments