## Lookup Function - Part I

Posted by
Sokhom
Labels:
Functions

The

**LOOKUP**function returns a value either from a one-row or one-column range or from an array. The**LOOKUP**function has two syntax forms: vector and array. The vector form of**LOOKUP**looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range. The array form of**LOOKUP**looks in the first row or column of an array for the specified value, and then returns a value from the same position in the last row or column of the array.### Vector Form of LOOKUP

The vector form of

**LOOKUP**looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range. Use this form of the**LOOKUP**function when you want to specify the range that contains the values that you want to match.#### Syntax for Vector Form

**LOOKUP(**

`lookup_value`,`lookup_vector`,`result_vector`)`Lookup_value`is a value that**LOOKUP**searches for in the first vector.`Lookup_value`can be a number, text, a logical value, or a name or reference that refers to a value.`Lookup_vector`is a range that contains only one row or one column. The values in`lookup_vector`can be text, numbers, or logical values.

**Important**The values in`lookup_vector`must be placed in ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do not do so,**LOOKUP**may not give the correct value. Uppercase and lowercase text are equivalent.`Result_vector`is a range that contains only one row or column. It must be the same size as`lookup_vector`.

**Note**

- If
**LOOKUP**cannot find the`lookup_value`, it matches the largest value in`lookup_vector`that is less than or equal to`lookup_value`. - If
`lookup_value`is smaller than the smallest value in`lookup_vector`,**LOOKUP**gives the #N/A error value.

Example | A | B |

1 | Frequency | Color |

2 | 4.14 | red |

3 | 4.19 | orange |

4 | 5.17 | yellow |

5 | 5.77 | green |

6 | 6.39 | blue |

Formula | Description (Result) |

=LOOKUP(4.91,A2:A6,B2:B6) | Looks up 4.19 in column A, and returns the value from column B that is in the same row (orange). |

=LOOKUP(5.00,A2:A6,B2:B6) | Looks up 5.00 in column A, and returns the value from column B that is in the same row (orange). |

=LOOKUP(7.66,A2:A6,B2:B6) | Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that is in the same row (blue). |

=LOOKUP(0,A2:A6,B2:B6) | Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A7 (#N/A). |

### Array Form of LOOKUP

The array form of

**LOOKUP**looks in the first row or column of an array for the value that you specify, and then returns a value from the same position in the last row or column of the array. Use this form of**LOOKUP**when the values you want to match are in the first row or column of the array.#### Syntax for Array Form

**LOOKUP(**

`lookup_value`,`array`)`Lookup_value`is a value that**LOOKUP**searches for in an array.`Lookup_value`can be a number, text, a logical value, or a name or reference that refers to a value.- If
**LOOKUP**cannot find the`lookup_value`, it uses the largest value in the array that is less than or equal to`lookup_value`. - If
`lookup_value`is smaller than the smallest value in the first row or column (depending on the array dimensions),**LOOKUP**returns the #N/A error value. `Array`is a range of cells that contains text, numbers, or logical values that you want to compare with`lookup_value`.

The array form of**LOOKUP**is similar to the**HLOOKUP**and**VLOOKUP**functions. The difference is that**HLOOKUP**searches for`lookup_value`in the first row,**VLOOKUP**searches in the first column, and**LOOKUP**searches according to the dimensions of`array`.- If
`array`covers an area that is wider than it is tall (more columns than rows),**LOOKUP**searches for`lookup_value`in the first row. - If
`array`is square or is taller than it is wide (more rows than columns),**LOOKUP**searches in the first column. - With
**HLOOKUP**and**VLOOKUP**, you can index down or across, but**LOOKUP**always selects the last value in the row or column.

**Important**The values in

`array`must be placed in ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do not do so,

**LOOKUP**may not give the correct value. Uppercase and lowercase text are equivalent.

##### Example

A | B | |

1 | a | 1 |

2 | b | 2 |

3 | c | 3 |

4 | d | 4 |

Formula | Description (Result) |

=LOOKUP("c",A1:B4) | Looks up "C" in first row of the array and returns the value in the last row that is in the same column (3). |

=LOOKUP("bump",A1:B4) | Looks up "bump" in first row of the array and returns the value in the last column that is in the same row (2). |

Subscribe to:
Post Comments (Atom)

Post a Comment