excel training microsoft

excel training microsoft - Lookup table

Resolved · Low Priority · Version Standard

Lookup table

Folusade has attended:
Excel Intermediate course
Excel Advanced course

by - delegate Folusade [1 post] (2008 Jan 24 Thu, 09:51) Reply

How do I use a lookup table

Version	Date	Location	Places available	Book	Next place rate:
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 4 Thu	Bayswater	2	book now	£195	£205
2007	2008 Dec 8 Mon	Bayswater	2	book now	£224	£224
2007	2008 Dec 9 Tue	Bloomsbury	0	FULL
2002/XP	2008 Dec 9 Tue	Tooting	0	FULL
2003	2008 Dec 11 Thu	Southwark	7	book now	£188	£215
2007	2008 Dec 16 Tue	Bayswater	6	book now	£207	£215
Full Schedule: See all 109 Excel Intermediate course dates. Bookings currently available until 25th November 2009.

RE: How to use Lookup

by - trainer Katie gold contributer [214 posts] (2008 Feb 6 Wed, 14:45) Reply

Hi Folusade,

Thanks for your question.

There are 2 lookup functions: VLOOKUP & HLOOKUP. Depending on whether your data list is lined up Vertically (VLOOKUP) or Horizontally (HLOOKUP).

Both are used to find a record from a large table.

For example, a 3-column table range from "A8" to "C20" stores Employee Info across column A to C with headings:
Employee No, Dept & Salary.

You can find info on a particular employee, using VLOOKUP.

=VLOOKUP (search cell address, data table range, result column number, match type)

In a sample scenario:
Set cell "A6" as search cell with employee no "E012"
Set data table between "A8 to C20"
Lookup the salary of employee No. "E012" - 3rd column of the data table
Matching exact Employee No.

The function will be as follows:
=VLOOKUP ("A6", "A8:C20", 3, false)

The answer will return the salary figure of employee "E012"

Regards,

Katie

· Microsoft Excel Training Course
· Microsoft Excel Features and Functions for Accountants
· Microsoft Excel Training: Not Just for Newbies Anymore
· Excel Course Tips and Tricks to Liven Up Your Excel Spreadsheets
· Tools From Advanced Excel Courses: Goal Seek and Solver

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips

Rate this page: