Free Essay

# Microsoft Excel Vlookup & Hlookup

Submitted By gagana
Words 892
Pages 4
V & H LOOKUP FUNCTION

These functions provide a means of reading individual data values from lookup tables (also known as arrays or ranges). Excel allows you to perform both vertical and horizontal lookups by using VLOOKUP and HLOOKUP functions.

Vertical lookup
Vertical lookup Searches the leftmost column of a lookup table for a specific “Search Value” and then returns a value contain in the intersecting cell of the relevant row and relevant column of the lookup table.
Note: Function abbreviation of Vertical lookup is “VLOOKUP”

Vlookup Syntax
VLOOKUP (lookup value, table array, column index number, Type)
Lookup Value – is the search value (i.e. the item being search for in the leftmost column of the table). This search value may either be a number, a text entry (which needs to be enclosed in double quotation marks), or a cell or range name.

Table Array – is the range reference or range name for the table containing the information (ie. Lookup Table).

Column Index num –is the column number in the table containing the information that is to be returned.

Type – is an optional argument for exact match or approximate match. • If True – Lookup function searches the left most column of the lookup table for the largest number less than or equal to the search value.

Note that if you do not enter the type, Lookup function takes True type as default type.

• If False - Lookup function searches for an exact match.

Important – Before you can perform the Lookup function it is important to sort the table array according to the values in the left most column in ascending order. Important – Ensure leftmost column in lookup table contains unique values.

Hint: whenever VLookup function uses a text entry (eg. “Monday”) as its search value, its type argument must be set to False, but the left most column of the table does not need to be sorted into ascending order.

Activity 1
How to write a formula to calculate tax rate for an income level
Income Level Tax Rate
Rs.0.00 – Rs.9.999.00 15%
Rs.10,000.00 – Rs.29,999.00 30%
Rs.30,000.00 – Rs.99,999.00 34%
Rs.100,000.00 and over 40%

Activity 2
Given a product ID, how can I lookup the product’s price
Product ID Price Rs.
A134 3.50
B234 4.20
F167 5.60
X567 5.90
R356 5.70
S123 4.20

Activity 3
Sales discounts should be calculated based on the sales amount and the selected discount plan. Four discount plans together with relevant sales ranges are given below.
|Sales |Plan 1 |Plan 2 |Plan 3 |Plan 4 |
|0 |0.0% |0.0% |0.0% |0.0% |
|20,000 |2.0% |3.0% |4.0% |4.8% |
|40,000 |2.1% |3.1% |4.2% |4.9% |
|60,000 |2.2% |3.2% |4.3% |5.0% |
|80,000 |2.3% |3.3% |4.4% |5.1% |
|100,000 |2.4% |3.4% |4.5% |5.2% |

Develop a model to calculate reverent discount amounts for given sales value and discount plan.

Activity 4
Use Vlookup function for the product repair decision example in the previous lesson.

Horizontal Lookup
HLookup is identical to VLOOKUP function, except it searches across the first row of the lookup table (rather than down its first column) for a given value.
Syntax
HLOOKUP (lookup value, table array, row index number, type)
You will notice that the only deference in the syntax of the HLOOKUP function compared to the VLOOKUP function is that it contains a row index number rather than a column index number. Other things are same as the application of VLOOKUP function.

Activity 5
Suppose that a product’s price changes over time. Following details shows the price charged in different time periods. I know the date the product was sold. How can I write a formula to see the product price charged in that period?
Date sold Price
January – April 2005 Rs.98
May – August 2005 Rs.105
September – December 2005 Rs.112
January -2006 onwards Rs.120

Example 1
The file “Hr data” gives employee ID codes, Salaries and years of experience. Write a formula that takes a given ID code and yields the employee salary. Write another formula that takes a given ID and yield the employee’s years of experience.

Example 2
1-5 Rs.12,000
6-10 Rs.11,000
11-20 Rs.10,000
More than 20 Rs.9,000

Example 3
Using the same data table now write a formula to calculate the advertising cost as given below.
If you by 8 ads then first 5 you need to pay 12000 per add and next 3 pay 11000 and so on

Example 4
Develop the database of your employees by using necessary information required to develop pay slip.
Develop pay slip for the employees of your company by entering employee’s number and extract the following information’s.
Name of the employee
Basic salary
Loans
OT payments
Loan deductions
EPF deductions of 10%
No pay dedications
Net salary
Company contribution to EPF of 15%
Company contribution of ETE of 3%

### Similar Documents

Free Essay

#### Lol Wtf

...Microsoft Excel LAPORAN PRAKTIKUM ( Laboratorium Computer ) Oleh : <<Nopian – 1801391053 >> Kelas : LA23 Ruang : 727 Management Laboratory Bina Nusantara University Jakarta Lembar Penilaian Laporan School of Business Management Jurusan Manajemen Bina Nusantara Matakuliah Kd. Matakuliah : Z0094-(COMP6152) Nama Mk : Computer Laboratory II Topik : Microsoft Excel Asisten Kode Asisten : MN155, MN173 Nama Asisten : Joshua Tanoko, Fernanda Losaura Mahasiswa NIM : 1801391053 Nama : Nopian Judul Laporan : Data Karyawan Toko Jam Rolex Penilaian Kriteria | Bobot | Nilai | Basic Formula (NOW TODAY,LEN,TEXT,RIGHT,MID, CONCATENATE, dll) | 5% | | Counting Formula (SUM, AVERAGE, MIN, MAX, dll) | 5% | | IF Function | 5% | | VLOOKUP & HLOOKUP | 15% | | Chart | 10% | | Sorting | 10% | | Filtering (Autofilter min 5 & Advanced filter) | 15% | | Consolidate (min 2) | 10% | | Data Validation | 10% | | Pivot Table | 15% | | Total | 100% | | Jakarta, 8 Juni 2015 __________________________ (Korektor) Contents Lembar Penilaian Laporan 2 KATA PENGANTAR 5 BAB 1 6 Latar Belakang 6 Tujuan 7 Rumusan Masalah 7 Bab 2 8 Inisial 8 Tahun Kerja 9 If Function 10 Vlookup Function 11 SUM 12 Average 13 Tabel 14 Diagram 15 Sorting 16 Filter 17 Advanced Filter 19 Consolidate 21 Sub Total 22 Data Validation 23 Pivot Table 24 KATA PENGANTAR Puji......

Words: 705 - Pages: 3

#### Excel Formulas

...Microsoft Excel: Fo rmulas, F or mulas.. . Northern New York Library Network 6721 US Hwy 11 Postdam, NY 13676 877-833-1674 or 315-265-1119 www.nnyln.org Jim Crowley C3 - Crowley Computer Consulting 9148 State Highway 37 Ogdensburg NY 13669 315-394-7008 fax 315-394-7009 www.crowleycomputers.com jim@crowleycomputers.com Description: Excel has hundreds of functions and nobody knows them all, but spend some time and learn to create more powerful formulas. Students will dive into decision making functions like if() and lookup() and make Excel work for them! Students will also explore efficient use of multiple sheets in a workbook, creating multi-sheet and multi-workbook formulas. Students will learn about: if() functions, lookup() functions, conditional formatting, and using multiple worksheets in a workbook. 1. Course objectives. a. To provide the class with an exploration of spreadsheet design. b. To develop computer and spreadsheet literacy. c. To address the class' questions. 2. Schedule a. 9:00 Registration b. 9:30 Morning session c. 12:00 Departure 3. Handouts available in Adobe Portable Document Format (PDF) for download at www.crowleycomputers.com/handouts.htm 1. 2. Formulas: All formulas begin with = or basic math function a. Simple operators Monthly weather (1) + averages.xls (2) (3) * multiply (4) / divide (5) ^ exponent (6) = < > comparison b. Order of operator precedence (or Algebraic Order of Operations, for those of you as old as me!) determines what order...

Words: 1729 - Pages: 7

#### Excel

...ADVANCED EXCEL TRAINING Quality Management Objective  To provide the participants with an understanding of the advanced features of MS Excel Today is 7/22/2011 2 Advanced  ® EXCEL Topics Working with Data Lists Naming Cells and Ranges   Conditional Functions Lookup Functions   Pivot Tables Data Validation  Today is 7/22/2011 3 Naming Cells and Ranges Today is 7/22/2011 4 Name cells by using labels Name cells by using existing row and column labels Select the range you want to name, including the row or column labels. On In the Insert menu, point to Name, and then click Create. the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box. Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels. Today is 7/22/2011 5 Name a Cell or Range Using Names Box  1. Select the cell or range you want to name.  2. Click the Name box on the formula bar.  3. Type a name for the range. A range name can include uppercase or lowercase letters, numbers, and punctuation, but no spaces. Try to use a simple name that reflects the type of information in the range, such as BookTitle.  4. Press Enter. The range name will appear in the Name box whenever you select the range. Today is 7/22/2011 6 Conditional......

Words: 1579 - Pages: 7

Free Essay

#### Carter Cleaning Company

...Penggunaan exel Boo Boo Wow Lead you smart !!! ------------------------------------------------- Top of Form Bottom of Form Pages * Beranda Sponsor FOREDI UNTUK TAHAN LAMA SEX REKOMENDASI BOYKE! | Merapatkan VAGINA yang KENDOR | TURUN 3-5 KG dalam SEMINGGU..! Simpelet 3 | invest hanya 100 ribu, penghasilan hingga 10 jt/bl | KumpulBlogger.com:Menerima Bitcoin | Selasa, 14 Mei 2013 artikel lengkap, contoh rumus fungsi excel lengkap, materi pelajaran, mudah murah lengkap simple, pengertian definisi ringkasan contoh lengkap fungsi rumus formula excel microsoft, tutorial mudah, wow Rumus Fungsi Microsoft Excel Lengkap Contoh dan Penjelasan Inilah 31 bahkan lebih contoh contoh serta penjelasan rumus ( formula ) dan fungsi ( function ) secara lengkap dan singkat yang biasanya digunakan pada microsoft excel ddari versi 2007 sampai 2010. Fungsi ini  secara acak ada fungsi text , fungsi logika , fungsi tabel , dan lain lain. Untuk yang belum tau apa itu rumus dan fungsi excel klik Pengertian Contoh Fungsi Rumus Excel Lengkap Microsoft.  Inilah : 1.        Fungsi VALUE         digunakan untuk merubah nilai value menjadi nilai text, penulisannya :        =VALUE(text) 2.        Fungsi FIND digunakan untuk menghasilkan posisi substring dari sebuah string atau suatu nomor yang dicari,penulisannya :        =FIND(cari text,pada text,mulai nomor) 3.        Fungsi MID digunakan untu mengambil karakter tertentu dari sederet karakter, penulisannya :......

Words: 2917 - Pages: 12

#### Srdr

Words: 10539 - Pages: 43

#### Aaaaaaaaaa

...MS-EXCEL FOR CHARTERED ACCOUNTANTS SUNIL B GABHAWALLA CHARTERED ACCOUNTANT WESTERN INDIA REGIONAL COUNCIL OF THE INSTITUTE OF CHARTERED ACCOUNTANTS OF INDIA TABLE OF CONTENTS 1 2 Basics & Beyond......................................................................................................... 4 1.1 Introduction......................................................................................................... 4 1.2 The EXCEL Screen............................................................................................. 4 1.3 Moving Around................................................................................................... 5 Data Entry ................................................................................................................... 8 2.1 Text ..................................................................................................................... 8 2.2 Number (including date, time, percent) .............................................................. 8 2.3 Formulae ............................................................................................................. 8 2.4 Functions............................................................................................................. 9 2.5 AutoComplete ..................................................................................................... 9 2.6 AutoCorrect........................................................................................

Words: 16077 - Pages: 65

#### Pussytitle

...Section 14: Data Analysis Mark Nicholls – ICT Lounge IGCSE ICT – SECTION 14 DATA ANALYSIS MICROSOFT EXCEL STEP BY STEP GUIDE Mark Nicholls ICT Lounge Section 14: Data Analysis Mark Nicholls – ICT Lounge Data Analysis – Self Study Guide Contents Learning Outcomes ……………………………………………………………………………………… Page 3 What is a Data Model? ..................................................................................... Page 4 Spreadsheet Basics…………………………………………………………………………………………. Page 4 – 6 Resizing Column Widths…………………………………………………………………………………. Page 6 Autofil…………………………………………………………………………………………………………….. Page 7 Absolute Cell Reference…………………………………………………………………………………… Page 8 Printing Spreadsheet Values………………………………………………………………………….. Page 9 Printing Spreadsheet Formulae……………………………………………………………………….. Page 10 Basis Formulae (Operators)……………………………………………………………………………. Page 11 – 13 Naming Cells and Cell Ranges………………………………………………………………………… Page 14 – 15 Introduction to Functions………………………………………………………………………………… Page 16 SUM Function……………………………………………………………………………………… Page 16 – 17 AVERAGE Function……………………………………………………………………………… Page 18 MAX Function…………………………………………………………………………………….. Page 18 MIN Function……………………………………………………………………………………… Page 19 Alternative to Typing in Functions………………………………………………………………….. Page 19 – 20 Activity 1 ………………………………………………………………………………………………………… Page 20 INT Function ………………………………………………………………………………………. Page 21 – 22 ROUND Function ……………………………………………………………………………….. Page......

Words: 5903 - Pages: 24

Free Essay

#### Excel 2010

...Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 0 Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam Mục lục Excel 2010 I. Những điểm mới trong Microsoft Excel 2010 .................................................................................. 7 Thêm tính năng Sparkline ........................................................................................................................ 8 Tính năng Slicers...................................................................................................................................... 9 Định dạng dữ liệu có điều kiện .............................................................................................................. 10 PivotTables và PivotCharts..................................................................................................................... 11 Share Workbook.................................................................................................................................... 12 I. Nội dung chính .............................................................................................................................. 12 Chương 1: Làm Quen Với Microsoft Excel ...................................................................................... 12 1.1 Giới thiệu Excel ...................................................................................................................... 12 Excel là gì:......

Words: 43133 - Pages: 173

#### Exel 2010 Formulas

Words: 49651 - Pages: 199

#### Super Man

...won’t find a more comprehensive book on Excel 2010 than this!” —Dick Kusleika, Microsoft MVP, DailyDoseOfExcel.com • Searchable PDF of the book • Understand functions, charts, worksheets, and workbooks System Requirements: See the CD Appendix in the book for details and complete system requirements. • Master “what-if” analysis, Goal Seeking, external database ﬁles, and pivot tables • Develop custom functions, program with VBA, and create UserForms • Try new slice-and-dice tools to dynamically ﬁlter your data Preview how your copied text will look www.wiley.com/compbooks Spot trends in your data with Sparklines John Walkenbach aka “Mr. Spreadsheet” is the principal of J-Walk and Associates, Inc. and a Microsoft Excel MVP. He is a leading authority on spreadsheet software and the creator of the award-winning Power Utility Pak. John has written more than 50 books, as well as articles and reviews for publications including PC World, InfoWorld, and Windows. He also maintains the popular Spreadsheet Page at spreadsheetpage.com. ® • All the examples and workbook ﬁles used in the book Microsoft® Follow the examples in the book, chapter by chapter, using the bonus materials on the CD-ROM: Excel 2010 What’s on the CD-ROM? • Get up to speed on everything new in Excel 2010 • Perform magic with array formulas—and even more with Sparklines CD-ROM Included John Walkenbach If Excel can do it, John Walkenbach can......

Words: 167105 - Pages: 669