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" cannot be found the value used by this function can be confusing.
* If "lookup_value" is smaller than the smallest value in "lookup_vector", then #N/A is returned.
* If "lookup_value" is between the smallest and largest values, the closest value that is less than it, is used.
* If "lookup_value" is larger than the largest value in "lookup_vector", then the largest value is used (which is the closest value that is less than it).
* 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.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=LOOKUP("Tokyo", B1:B3, C1:C3) = 6	Paris	8
2	=LOOKUP("Paris", B1:B3, C1:C3) = 8	Rome	11
3	=LOOKUP("Rome", B1:B3, C1:C3) = 11	Tokyo	6
4	=LOOKUP("Before-Paris", B1:B3, C1:C3) = #N/A
5	=LOOKUP("Paris-After", B1:B3, C1:C3) = 8		Tokyo	6
6	=LOOKUP("Tokyo-After", B1:B3, C1:C3) = 6		Rome	11
7	=LOOKUP("Tokyo", C5:C7, D5:D7) = 8		Paris	8
8	=LOOKUP("Paris", C5:C7, D5:D7) = #N/A
9	=LOOKUP("Rome", C5:C7, D5:D7) = 11	Paris	Rome	Tokyo
10	=LOOKUP("Tokyo", B9:D9, B10:D10) = 6	8	11	6
11	=LOOKUP("Paris-After", B9:D9, B10:D10) = 8

1 - Return the value in the range "B1:B3" that corresponds to the position of "Tokyo" in the range "C1:C3".
2 - Return the value in the range "B1:B3" that corresponds to the position of "Paris" in the range "C1:C3".
3 - Return the value in the range "B1:B3" that corresponds to the position of "Rome" in the range "C1:C3".
4 - Return the value corresponding to the position of "Before-Paris". The value "Before-Paris" is smaller than the smallest value so it returns #N/A.
5 - Return the value corresponding to the position of "Paris-After". The value "Paris-After" is between the smallest and largest values, so it uses the closest value that is less than "Paris-After", which is Paris (8).
6 - Return the value corresponding to the position of "Tokyo-After". The value "Tokyo-After" is larger than the largest value so the largest value is used, which is Tokyo (6).
7 - Return the value in the range "C5:C7" that corresponds to the position of "Tokyo" in the range "C5:C7".
Incorrect. The 1st column is not sorted. The value "Tokyo" is in the list so returning "Paris" is very confusing.
8 - Return the value in the range "C5:C7" that corresponds to the position of "Paris" in the range "C5:C7".
Incorrect. The 1st column is not sorted. The value "Paris" is in the list so returning #N/A is very confusing.
9 - Return the value in the range "C5:C7" that corresponds to the position of "Rome" in the range "C5:C7".
Incorrect. The 1st column is not sorted. The value "Rome" is in the list so returning "Rome" seems to be correct which is very confusing.
10 - Return the value in the range "B9:D9" that corresponds to the position of "Tokyo" in the range "B10:D10".
11 - Returns the value corresponding to the position of "Paris-After". The value "Paris-After" is between the smallest and largest values, so it uses the closest value that is less than "Paris-After", which is Paris (8).