LOOKUP Functions in Excel to Get Required Data to Desired Range

Home/Excel Formulas/LOOKUP Functions in Excel to Get Required Data to Desired Range

Excel is well know spreadsheet applications, some features are very powerful in Excel. We can say, LOOKUP Formulas are one of these powerful features in Excel. LOOKUP functions saves lots of time and we will get the desired output with one single formula. Think if you are not provided these functions in Excel, we have to code at least a 20 lines complex algorithm to perform the same task using any programming language.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Let’s see,Why are these LookUp Formulas Very Powerful?

Importance of LookUp Formulas

Assume that we have half a million records in a worksheet with Pin Codes and City Names which are Big Cities and Towns in the World. And You have to find the City names of 20 Pin Codes every time the data changes. You may think that we can easily find using Find tool available in the Excel. You may be correct!, but think that if data changes in every 5 minutes? It’s difficult to find right?? And we get irritation to use find tool those many times.

It is a simple example to make you understand the use of LookUp Formulas.In real-time, these LookUp formulas are very helpful and handy while you are working with the data. You will save lot of processing time and you will not feel that you are really doing a complex tasks.

Here is the real-time Example:

There are more than 4000 store across the world for one of the leading Retailers in the world. The data for all the stores are available in an Excel Worksheet with Store Number, Unites Sold and Sales. This data is update and every day it will be refreshed. And the Business Analyst wants to understand the Sales of some store numbers quickly. So, Business Analyst or Executive can quickly get the using LookUp function.

What are different Lookup Functions available in Excel?

LOOKUP Functions in Excel search for values within a table or a list and returns a value. There are three Lookup functions and each function uses a different method for searching and return a value:

  • LOOKUP: Returns the value in a row or column that matches a value in a row or column.
  • VLOOKUP: Returns the value in a column that matches a value in the first column of a reference table.
  • HLOOKUP: Returns the value in a row that matches a value in the top row of a reference table.

Other Excel Formulas which we can use with LookUp Functions

These three LookUp functions discussed above alone may not sufficient to perform some kind of tasks. We combine these LookUp functions with the following functions to improve the LookUp capabilities and achieve desired tasks.

  • CHOOSE: Returns the value from a list of values, based on an index number.
  • INDEX: Returns the value from a table, based on an index number.
  • MATCH: Returns the position of a value in an array or list.
  • OFFSET: Returns the cell value which is an offset from a given cell reference.
 
 
Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |February 1st, 2013|Excel Formulas|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


excel vba variable typesvba conditionexcel worksheet copyhow to unprotect excel worksheetexcel copy cellsadvanced vlookupunhide sheets in excel 2007vlookup in excel formulasheets copy vbaaccess vba input boxisna excel functionvba message boxhow to unhide the hide columns in excelrefresh pivot table macroexcel hide duplicatesexcel function sumifcomment in excel vbahow to create defined names in excelvba activeworkbook.saveconsolidate data in multiple worksheetsexcel vba propertyvba chartexcel to xml conversionexcel vba if cell containsvlookup formula in excelexcel vba with rangeprint macro excelwhat is concatenate excelduplicate check in excelexcel vbsvba selection.pastespecialhow to enable macros in excel sheethow to create pivot table in excel 2007 with exampleusing the sumif functionconverting excel to xmlbasic vbapowerpoint dashboard templateexcel unprotected formulaiif in excelhide and unhide columns in excelhow to sort an excel spreadsheetexcel macro rangehide worksheetvba createtextfileexcel formulas sumif exampleshow to use vlookup excel 2013how to do vlookup on excel 2010excel identify duplicate rowslength of array vbasample dashboard in excelhow to unlock excel spreadsheet 2010 without passwordexplain cut copy and pastenew line in vbaautomatic row height excellast cell in column vbaproject management gantt chart excelfor each in vbamsgbox vba yes nomsgbox functionwhat is a pivot table in excel 2013how to make radio buttons in excelwhat is sumif in excelweekly calendar template 2014 excelpivot table templateexcel vba waitworkbook and worksheet in ms excelexcel macro string concatenationexcel iserrorcomplex macros in excelinsert a checkbox in excel 2007excel dashboard templates freeexcel macro conditional statementuserform word 2010how to unhide columns in excel 2007formula for merging cells in excelthe vlookup functionselect sheet excel vbavb questions and answersfor loop step vbavb color indexhow to enter a carriage return in excel