LOOKUP(lookup_value, lookup_vector, result_vector)

Array Form - 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 look for.
array(Array Form Only) The array of values that contain the value you want to look for.
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.

Remarks

* 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.
* This function does not have an argument to indicate exact match or closest match.
* This function will always return the Closest Match when there is no Exact Match.
* 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 it uses the largest value that is less than "lookup_vector". Example 12
* If "result_vector" is not the same size as "lookup_vector", you might still get the correct answer.
* The vector form of this function looks in a one-row or one-column range for a value and returns a value from the same position in a second one-row or one-column range.
* The array form of this function looks in the first row or column for a value and returns a value from the same position in the last row or column.
* The "array" can be positive or negative (i.e. rows up or down).
* The "array" argument must be sorted into ascending order (-1, 0, 1, A-Z, FALSE, TRUE).
* 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.
* For the Microsoft documentation refer to support.office.com

 ABC
1=LOOKUP("Bolts",B1:B4) = BoltsWheels6
2=LOOKUP("Screws",B1:B4) = NutsBearing8
3=LOOKUP("bolts",B1:B4) = BoltsBolts11
4=LOOKUP("b",{"a","b","c"}) = bNuts9
5=LOOKUP("b",{"a","b","c";1,2,3}) = 2  
6=LOOKUP("B",{"a",1;"b",2;"c",3}) = 2  
7=LOOKUP("b",{"a",1,"+";"b",2,"-";"c",3,"*"}) = -  
8=LOOKUP(B1,B1:B4) = Nuts  
9=LOOKUP("b",{"a","b","c";1,2,3;"+","-","*"}) = *  
10=LOOKUP("bump",{"a",1;"b",2;"c",3}) = 2  
11=LOOKUP("C",{"a",1;"b",2;"c",3;"d",4}) = £3.00  
12=LOOKUP("C",{"a","b","c","d";1,2,3,4}) = 3  
13=LOOKUP("banana",{"a",1;"b",2;"c",3}) = 2  
14=LOOKUP("Wheels",{"V","W","X"}) = W  
15=LOOKUP("Wheels",{1,2,3}) = #N/A  
16=LOOKUP("Bearing",B1:B4,C1:C4) = 8  
17=LOOKUP("Nuts",B1:B4,C1:C4) = 9  
18=LOOKUP("Nuts",B1:C4,C1:C4) = 9  
19=LOOKUP("b",{"a","b","c","d"},{1,2,3,4}) = £2.00  
20=LOOKUP("Wheel",B1:B4,C1:C4) = 9  
21=LOOKUP("Wheels",B1:B3,C1:C2) = 11  
22=LOOKUP("Nuts",B1:C4,B1:C4) = #N/A  
23=LOOKUP("Nuts",B1:B2,B1:B4) = #N/A  

1 - (array) If the array is 1 dimensional and a match is found then the lookup value is returned.
2 - (array) If the array is 1 dimensional and a match is not found then the first value from the array is returned.
3 - (array) This is the same as 1 and shows that this function is not case sensitive.
4 - (array) Always returns the value from the same position in the last row (or column).

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