1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Look-Up Formula?

Discussion in 'Business Applications' started by gevans, Jul 12, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. gevans

    gevans Thread Starter

    Joined:
    Aug 26, 2004
    Messages:
    99
    I am trying to insert a lookup formula so that -- when I insert an ID # on sheet 1 -- it will pull in the 1st & last name of the individual from a list on sheet 2 -- I have attached an example. I thought I had it -- but no luck so far. I'm pretty new to this particular formula!
     

    Attached Files:

  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    There you go

    I have given the data range a name called Names. The Vlookup refers to that.

    If you have to insert any new people in the data sheet, insert a new row in the middle of the date and input the new info. This will expand the range name called Names for you and you will not need to change any fomulas later on.
    When you have done that, sort on column A to make sure the data is in numerical order otherwise the Vlookup won't work
     

    Attached Files:

  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Here is an update, trapping the problem of the number not existing. See second record.
     

    Attached Files:

  4. gevans

    gevans Thread Starter

    Joined:
    Aug 26, 2004
    Messages:
    99
    Thanks so much -- it's working great!
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    can you click on the Thread Tools Icon at thetop of this thread and mark it solved please.

    Come back if you get any errors, as it might need further error traps. However, as they say "If it ain't broke, don't fix it" :)
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If you add another named range called BigStr, defined as ...

    =REPT("z",255)

    .. then you can adjust the named range Names to ..

    ='Data Sheet'!$A$1:INDEX('Data Sheet'!$E:$E,MATCH(BigStr,'Data Sheet'!$E:$E))

    .. and the named range will grow with your data whether you insert in the middle, top or bottom.

    HTH
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Thats a nice one. :)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/482533

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice