LOOKUP

LOOKUP(lookup_value, lookup_vector, result_vector)

Array Form Syntax - LOOKUP(lookup_value, array)

Returns the value in a row (or column) that matches a value in a column (or row).

lookup_valueThe value you want to find.
lookup_vectorThe range of cells that contains the value you want to look for.
result_vectorThe range of cells that contains the value you want to return.
array(Array Form Only) The array of values that contain the value you want to look for.

REMARKS
* XLOOKUP was added in Microsoft 365 to replace this function.
* For an illustrated example refer to the page under Lookup Functions
* This function is not case sensitive when searching for text strings.
* This function can return a value either from a one-row or one-column range or from an array.
* The "lookup_value" can be a number, text, logical value, or a name or reference that refers to a one of these.
* If "lookup_value" is smaller than the smallest value in "lookup_vector", then #N/A is returned.
* If "lookup_value" cannot be found, then the next smallest value is always returned.
* If "lookup_value" cannot be found, this function cannot return #N/A.
* The "lookup_vector" must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE).
* If "result_vector" is not the same size as "lookup_vector", you might still get the correct answer.
* The vector form looks in a one-row (or one-column) range for a value and returns the value from the same position in the second range.
* The array form looks in the first row (or column) for a value and returns the value from the same position in the last row (or column).
* You can use the XMATCH function to return the position of a value.
* You can use the HLOOKUP function to search for a value and return a different item from the same column.
* You can use the VLOOKUP function to search for a value and return a different item from the same row.
* 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.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=LOOKUP("Wheels",B1:D1, B2:D2) = 6BearingsBoltsWheels
2=LOOKUP("Bearings",B1:D1, B2:D2) = 88116
3=LOOKUP("Bolts",B1:D1, B2:D2) = 11   
4=LOOKUP("NoMatch",B1:D1, B2:D2) = 11Bearing8 
5=LOOKUP("Wheels",B4:B6, C4:C6) = 6Bolts11 
6=LOOKUP("Bearings",B4:B6, C4:C6) = 8Wheels6 
7=LOOKUP("bolts",B4:C6) = 11   
8=LOOKUP("b",{"a",10;"b", 20;"c",30}) = 20   

1 - Return the value in the 2nd row from the "Wheels" column. The first row is sorted. This value is correct.
2 - Return the value in the 2nd row from the "Bearings" column. The first row is sorted. This value is correct.
3 - Return the value in the 2nd row from the "Bolts" column. The first row is sorted. This value is correct.
4 - Return the value in the 2nd row from the "NoMatch" column. There is no match. This value is wrong.
5 - Return the value in the 2nd column from the "Wheels" row. The first column is sorted. This value is correct.
6 - Return the value in the 2nd column from the "Bearings" row. The first column is sorted. This value is correct.
7 - Return the value in the last column of the array from the "bolts" row. Not case sensitive. This value is correct.
8 - Return the value in the last column of the array from the "b" row.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top