Best Microsoft Excel Bloggers

Monday, November 18, 2013

Using Vlookup with other functions


LOOKUP FUNCTION

Woman Looking As I mentioned, I am writing a new Ebook on Lookup Functions and am having a blast playing around with some of these formulas.
 I started with LOOKUP. I have always wondered about it as I have always used VLOOKUP and HLOOKUP but had never seen LOOKUP used.

The Excel LOOKUP function has two forms: the Vector Form and the Array Form. Today, I am just going to talk about the vector form. This 
allows you to lookup a single value from a column. 

The lookup function is pretty straight-forward although you would never know it from its syntax.
=LOOKUP(Lookup_Value), lookup_vector, result_vector)
So, in English  this translates as:
 =LOOKUP(value you looking up, column that contains the value you are looking up, the column that contains the answer).
In the example below, I have an employee listing and I want to find the salary of Hamilton.  So, I am looking up Hamilton which is in Column A; that takes care of the first 2 parts of the syntax – the lookup value is A5 or “Hamilton” and the lookup_vector is Column A.
 The result or answer I am looking for is in Column B – Salary.

The equation then is =LOOKUP("Hamilton",A4:A9,B4:B9)
 




Now, this seems pretty simplistic with only 6 rows of information but you would find it useful if you had a couple of hundred or more employees. The difference between this lookup function and VLOOKUP or HLOOKUP is that there is no separate lookup table; instead, you are looking in the data itself.  


2 comments:

  1. Good article! Lookup seems to be a wonderful function.. only problem is that the list needs to be sorted - i.e. there is no option to return an error if the exact value isnt found

    ReplyDelete
  2. I am aware that functions like vlookup, match, index or sumproduct takes care of this issue.. Thing is that lookup might have been a great formula to use if it was able to perform an exact match

    ReplyDelete


Ms. Excel- Resident Excel Geek