Free Essay

Microsoft Excel Vlookup & Hlookup

In: Computers and Technology

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
You are thinking of advertising your products on a sports telecast. As you buy more ads, the price of each ad decreases as shown in the table.
Number of ads Price per ad
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
Grade
Basic salary
Advances
Loans
OT payments
Advance deductions
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

Premium Essay

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

Premium Essay

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

Premium Essay

Srdr

...AT Computer Labs Advanced Microsoft Excel 2013 Introduction Microsoft Excel is program designed to efficiently manage spreadsheets and analyze data. It contains both basic and advanced features that anyone can learn. Once some basic features are known, learning the advanced tools becomes easy. This lesson is composed of some advanced Excel features. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization. Objectives 1) Learn how to Customize the Interface 2) Advanced Formatting: Custom Lists, Cell Groups, and Transposing Tables 3) Learn how to Reference Across Sheets 4) Advanced Formulas and Using Data Ranges 5) Using Data Validation AT Computer Labs Interface Customization Most of Excels interface can be customized to fit many people’s needs. For some, customization makes tools more readily available by placing those tools in a location that is more natural for the user. This section will introduce you to customizing Excel’s interface by adding a tab in the ribbon, customizing the status bar at the bottom of the program, opening separate panes, and scrolling through a sheet with a static column or row. Adding a Tab in the Ribbon In this subsection, we will explore the tab options in Excel. Tabs in Excel can be added, deleted and even reorganized. This feature is......

Words: 10539 - Pages: 43

Premium Essay

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

Premium Essay

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

Premium Essay

Exel 2010 Formulas

...Excel 2010 Microsoft ® ® ® Formulas John Walkenbach BONUS CD-ROM! Includes all Excel workbook files used in the book, plus the complete book in a searchable PDF file Excel® 2010 Formulas by John Walkenbach Excel® 2010 Formulas Published by Wiley Publishing, Inc. 111 River Street Hoboken, NJ 07030-5774 www.wiley.com Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana Published by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 7486011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions. Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, and related trade dress are......

Words: 49651 - Pages: 199

Premium Essay

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 files, and pivot tables • Develop custom functions, program with VBA, and create UserForms • Try new slice-and-dice tools to dynamically filter 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 files 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

Premium Essay

Financial Modelling

...Advanced Modelling in Finance using Excel and VBA Mary Jackson and Mike Staunton JOHN WILEY & SONS, LTD Chichester ž New York ž Weinheim ž Brisbane ž Singapore ž Toronto Copyright  2001 by John Wiley & Sons, Ltd, Baffins Lane, Chichester, West Sussex PO19 1UD, England National 01243 779777 International (C44) 1243 779777 e-mail (for orders and customer service enquiries): cs-books@wiley.co.uk Visit our Home Page on http://www.wiley.co.uk or http://www.wiley.com All Rights Reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except under the terms of the Copyright, Designs and Patents Act 1988 or under the terms of a licence issued by the Copyright Licensing Agency, 90 Tottenham Court Road, London W1P 9HE, UK, without the permission in writing of the publisher. Other Wiley Editorial Offices John Wiley & Sons, Inc., 605 Third Avenue, New York, NY 10158-0012, USA Wiley-VCH Verlag GmbH, Pappelallee 3, D-69469 Weinheim, Germany John Wiley & Sons Australia Ltd, 42 McDougall Street, Milton, Queensland 4064, Australia John Wiley & Sons (Asia) Pte Ltd, 2 Clementi Loop #02-01, Jin Xing Distripark, Singapore 129809 John Wiley & Sons Canada Ltd, 6045 Freemont Blvd, Mississauga, ONT, L5R 4J3, Canada British Library Cataloguing in Publication Data A catalogue record for this book is available from the......

Words: 57326 - Pages: 230

Free Essay

Creative Atif Aslam

...Overview of Applications by Discipline ECONOMICS Estimating sensitivity of demand to price 352–356 Pricing problems 352–366, 422–427 Estimating cost of power 363–366 47–56, Assessing a utility function 554–556 Estimating demand for products 632–638, 649–650, 764–771, 965 Subway token hoarding 792 FINANCE AND ACCOUNTING Collecting on delinquent credit accounts 14–16 Cost projections 29–33 Finding a breakeven point 33–41 Calculating NPV 57–62 Calculating NPV for production capacity decision 58–62 Portfolio management 173–178, 345–346, 387–394, 442–444, 689–691 Pension fund management 178–182 Financial planning 210–214, 676–681, 734–735 Arbitrage opportunities in oil pricing 215–219 Currency trading 220 Capital budgeting 290–295 Estimating stock betas 396–401 Hedging risk with put options 407–408 Stock hedging 407–408 Asset management 409–410 New product development 503–504, 574, 673–676, 715–722 Bidding for a government contract 513–518, 523–533, 653–657 Investing with risk aversion 557–560 Land purchasing decision 575 Risk analysis 582–583 Liquidity risk management 651–653 Estimating warranty costs 657–661 Retirement planning 681–685 Modeling stock prices 685–686 Pricing options 686–689, 691–693 Investing for college 732 Bond investment 733 HUMAN RESOURCES AND HEALTH CARE Fighting HIV/AIDS 23–24 DEA in the hospital industry 184–189 Salesforce allocation problems 454–456 Assigning MBA students to teams 462 Selecting...

Words: 68271 - Pages: 274