Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Lookup Function - Tips please? (Excel 97)

    Hello

    Could someone point me in the direction of a place to learn about using the Lookup function in excel? I am also battling with understanding relative and absolute. I am sure some kind person has taken the time to write some clear and concise instructions out there on the Web? Even some examples would be useful.

    Also, is there a way to see graphically, in Excel, how a formula relateds to cells? Sorry if this is a stupid question.

    Kerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Hi Kerry, about your last question: select Tools | Auditing | Show Auditing Toolbar. This toolbar contains several buttons that can show how cells are related.
    Click Trace Precedents to see the "parent cells" of the active cell (cells referred to in the formula of the active cell). Click the button again to see the "grandparents" etc.
    Click Trace Dependents to see the "child cells" of the active cell (cells whose formula refer to the active cell). Click the button again to see the "grandchildren" etc.
    There are also buttons to remove precedents arrows or dependents arrows or all arrows.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Thanks Hans.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    About absolute and relative: this determines what happens if you copy and paste a formula to another cell, or use the fill grip in the lower right corner of a cell to copy the formula of the active cell down or to the right.

    An absolute reference will not be changed by Excel, while a relative reference will be adapted. To make a reference absolute, put $ signs in front of the row and or column, or press F4. A example may help.

    <table border=1><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B1</td><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B2</td><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B3</td></table>
    In the table above, the formula =$A$1+$B$1 was entered in the top left cell, then filled down. As you can see, the cells in the second and third row of the left column have exactly the same formula.

    The formula =$A$1+B1 was entered in the top right cell, then filled down. As you can see, the reference to A1 stays the same (because of the $ signs), but the reference to B1 is adapted to B2 and B3, respectively.

    This example also shows that you can mix absolute and relative references in one formula. You can even mix them within the reference to one cell: in $A1, the reference to the column is absolute, but the reference to the row is relative. In fact the F4 key mentioned above will cycle between the four possible ways of referring to a cell:

    <table border=1><td>Formula</td><td>Column</td><td>Row</td><td align=right>=A1</td><td>relative</td><td>relative</td><td align=right>=$A$1</td><td>absolute</td><td>absolute</td><td align=right>=A$1</td><td>relative</td><td>absolute</td><td align=right>=$A1</td><td>absolute</td><td>relative</td></table>
    Note: references within a workbook (even to cells in another worksheet) are relative by default, whereas references to cells in another workbook are absolute by default.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    The online help for Excel 97 does a reasonable job of explaining worksheet functions. If you search Google using (for instance) excel lookup function, you'll get lots of hits. On educational sites (.edu) you have a good chance of finding explanations and examples.

    Note: Excel has several lookup functions: LOOKUP, VLOOKUP and HLOOKUP (besides, MATCH and INDEX are related functions). In many cases, VLOOKUP and HLOOKUP are better.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Once again you have been extremely helpful Hans. This is a great explanation!

    As for the lookup functions - yes I will have a search on Google.

    Much appreciated.

    Regards Kerry

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Further to absolute and relative functions, an analogy that works well if you are explaining it to other people is giving someone directions.
    Absolute directions - means you give the address. Boom, that is where they are going.
    Relative directions - means you give the directions as follows; go two blocks north and turn left. Where you end up is relative to where you started from.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    (Edited by HansV to make url live - see <!post=Tags to make a URL live,98472>Tags to make a URL live<!/post> and <!help=19>Help 19<!/help>)

    Kerry:

    You might try Peter Noneley's Excel Function Dictionary spreadsheet available free at http://homepage.ntlworld.com/noneley/

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Thankyou Tony - This is absolutely FANTASTIC! I knew there would be some excellent stuff out there! Dont you just love this forum? Everyone is so helpful. I only hope I can offer something back soon.

    Regards

    Kerry

  10. #10
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Function - Tips please? (Excel 97)

    Kerry - You're welcome. Presumably you already have, since you're a star!

    Hans - Thanks for 'enlivening' my post. I'm fairly new to the lounge, so not aware of all its facilities yet. May I add my congratulations to all the others you have received.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •