Lookup Function – Tips please? (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans.

Reply | Quote

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.

=$A$1+$B$1	=$A$1+B1
=$A$1+$B$1	=$A$1+B2
=$A$1+$B$1	=$A$1+B3

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:

Formula	Column	Row
=A1	relative	relative
=$A$1	absolute	absolute
=A$1	relative	absolute
=$A1	absolute	relative

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

(Edited by HansV to make url live – see Tags to make a URL live and Help 19)

Kerry:

You might try Peter Noneley’s Excel Function Dictionary spreadsheet available free at http://homepage.ntlworld.com/noneley/%5B/url%5D

Tony.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote