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_value	The value you want to find.
lookup_vector	The range of cells that contains the value you want to look for.
result_vector	The 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

	A	B	C	D
1	=LOOKUP("Wheels",B1:D1,B2:D2) = 6	Bearings	Bolts	Wheels
2	=LOOKUP("Bearings",B1:D1,B2:D2) = 8	8	11	6
3	=LOOKUP("Bolts",B1:D1,B2:D2) = 11
4	=LOOKUP("NoMatch",B1:D1,B2:D2) = 11	Bearing	8
5	=LOOKUP("Wheels",B4:B6,C4:C6) = 6	Bolts	11
6	=LOOKUP("Bearings",B4:B6,C4:C6) = 8	Wheels	6
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.