Excel Lookup function - Search a name and show it's vaue

This article will explain what is Lookup function in Microsoft excel and how to use Lookup function with example.

LOOKUP function is a very useful function in Microsoft Excel which returns  a value from a range of values(a row or a column) or from an array.

There are two forms of the function.

1. Vector form
2. Array Form

1. Vector Form
-------------------

In this form, the function searches for the lookup_value in a range, i.e. a row or a column and when a match is found, it takes the corresponding result_value from another range specified (row or column).

Note: The data in lookup_range must be in sorted order. Otherwise Lookup function will give incorrect result.

Syntax:

=LOOKUP(lookup_value, lookup_range,[result_range])

result_range is optional.

Example:

Here are some more examples and the corresponding result:

=LOOKUP(104,A2:A5)              returns 104

=LOOKUP(103,A2:A5,B2:B5)     returns Binay

2. Array Form

The array form, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

Syntax:

=LOOKUP(lookup_value,array)

Example:

=LOOKUP("B", {"A","B","C","D";10,11,12,13})    returns 11 =LOOKUP("B", {"a","b","c","d";10,11,12,13})   returns 11
=LOOKUP("b", {"A","B","C","D";10,11,12,13})   returns 11
=LOOKUP("E", {"A","B","C","D";10,11,12,13})   returns 13
=LOOKUP("Back", {"A","B","C","D";10,11,12,13})   returns 11
=LOOKUP("0", {"A","B","C","D";10,11,12,13})   returns #N/A

For more Tips & Tricks in Excel, Click Here.