XLOOKUP |
| XLOOKUP(lookup_value, lookup_array, return_array [,not_found] [,match_mode] [,search_mode]) |
Returns the value in the same row after finding a matching value in any column. |
| lookup_value | The value to find in the lookup_array. |
| lookup_array | The array of values that contain the value you want to look for. |
| return_array | The range of cells that contains the value (or values) you want to return. |
| not_found | (Optional) The value to return when no match is found. |
| match_mode | (Optional) A number indicating the type of match: 0 (False) = Exact match, lookup_array does not have to be sorted (default) -1 = Closest match, exact or the next smallest 1 (True) = Closest match, exact or the next largest 2 = Wildcard character match (? and *) |
| search_mode | (Optional) A number indicating the search mode: 1 = Linear search, from first to last (default) -1 = Linear search, from last to first 2 = Binary search, lookup_array must be sorted into ascending order -2 = Binary search, lookup_array must be sorted into descending order |
| REMARKS |
| * This function is not case sensitive when matching text strings. * This function was added in Excel 2021 to replace the VLOOKUP, HLOOKUP and LOOKUP functions. * For an illustrated example refer to the page under Lookup Functions * This function supports wildcards (? and *). * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~). * If "lookup_value" cannot be found and "match_mode" = 0, then #N/A is returned. * If "lookup_array" is a two-dimensional range and not a single column (or row) reference, then #VALUE! is returned. * If "lookup_array" contains a different number of items to "return_array", then #VALUE! is returned. * If "not_found" is left blank and the function cannot find a match, then #N/A is returned. * If "match_mode" is left blank, then 0 is used. * If "match_mode" is not 0,1,-1,2,True or False, then #VALUE! is returned. * If "search_mode" is left blank, then 1 is used. * You can use the XMATCH function to return the position of a value. * You can use the INDEX function to return the intersection of a row and a column. * You can use the MATCH function to return the position of a value. * You can use the OFFSET function to return a value in an offset position. * You can use the CHOOSE function to return the value in a list based on an index number. * You can use the EXACT function to perform a case sensitive match. * For the Microsoft documentation refer to support.microsoft.com * For the Google documentation refer to support.google.com |
|
| 1 - Find the row containing "Wheels" in column "B" and return the value from column "C". 2 - Find the row containing "Bearings" in column "B" and return the value from column "C". 3 - Find the row containing "BEARINGS" in column "B" and return the value from column "C". This function is not case sensitive. 4 - Find the row containing "BEARINGS" in column "B" with a case sensitive match. Instead of True you could also use 1. 5 - Find the row containing "Wheels" in column "B" and return the value from column "B". 6 - Find the row containing "Wheel" in column "B" and if there is no match, return the string "NotFound". 7 - Find the closest match (or next smallest) to the value "Wheel" in column "B". 8 - Find the closest match (or next largest) to the value "Wheel" in column "B". 9 - What happens when you pass True as the match mode. The closest match (or next smallest) is returned. 10 - Find the column containing "Bolts" in row 5 and return the value from row 6. |
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top