Home > Excel Help Portal > Doing Lookups in Excel

<<  (Page 1)  Previous              Next  (Page 1)  >>

Doing Lookups in Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses using lookups in
Microsoft Excel formulas.

1. Insert Rows using a Macro I wanted to be able to insert a row and maintain the formulas but not to copy data that would be unique for the line. The propagation of data would be misleading so I want it to be blank so I have to enter it myself. See also use of OFFSET in formulas which simplifies the insertion, and deletion of... Keywords: Criteria, Sorting, Dates, Days, Colors, Format, Errors, Formulas, Printing, VBA, Macro, Files, Toolbar, Web, Comments, OFFSET, ROW, INDIRECT, VLOOKUP, SIGN, MID, INDEX, MAX

 
2. Summarizing Data Examples (an Overview) Conditional Format: =MOD(SUBTOTAL(3,$A$1:$A1),3)=0Shows color banding working with/without Filtering, but it does require that there be something in Column A for every row. More on Color Banding on my Conditional Formatting page. Filters, Validation, and Conditional Formatting are very similar to... Keywords: Charts, Advanced Filter, AutoFilter, Criteria, Rank, Days, Download, Borders, Colors, Conditional Formating, Fonts, Gridlines, Outline, Arrays, Errors, Formulas, Links, PivotTables, VBA, Macro, UDF, Web, Comments, MOD, COUNTIF, SUBTOTAL, MATCH, VLOOKUP, ROWS, COUNTA

 
3. VLOOKUP Worksheet Function This is the simplest example that I can come up with. Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used. For VLOOKUP the first column of the range is the used to match the... Keywords: Criteria, Format, Arrays, Errors, Formulas, Defined Name, VBA, Macro, Hyperlinks, Web, Comments, VLOOKUP, INDEX, MATCH, MAX, OFFSET, CHOOSE

 
4. How to Select Cells/Ranges Using Visual Basic Procedures in Excel When you create a Microsoft Visual Basic for Applications macro or procedure, you can choose from a variety of methods for selecting cells, ranges, and named ranges. This article contains sample code that demonstrates some of these methods.... KB#291308. Keywords: Sales, Errors, Class, Macro, Object, VBA, Web

 
5. SNAKECOLS, How to snake columns to use fewer pages Creating a Table from something like a column of address labels (#snkAddr) Simple example to take a single column, such as labels with Name; Street Address; City, State, Zipcode and convert to a table. On a second sheet, then use fill-handle for 3 lines per set start with: A1:... Keywords: Add-In, Format, Formulas, Printing, VBA, Macro, Files, HTML, Web, Comments, OFFSET, INDIRECT, INDEX, TRIM

 
6. Overview of Excel 2002 SP2 Update: October 16, 2002 This update permits the Ask to update automatic links option to override any attempts from a malicious user to suppress the warning dialog that appears in a Microsoft Excel workbook when a Microsoft Excel spreadsheet contains links to external... KB#329750. Keywords: Dates, Download, OLE, Links, PivotTables, Macro, CD-ROM, Files, HTML, Hyperlinks, Web

 
7. Build TOC Another Approach This page is not concerned with Hyperlinks, if you want a Table of Contents with hyperlinks then see my original page Build Table of Contents with BuildTOC dealing with creating a Table of Contents with hyperlinks, and with making, revealing, and destroying Hyperlinks. Extra code/column added to be... Keywords: Charts, Consolidate, Sorting, Format, Errors, Formulas, Links, VBA, Macro, User Defined Function, Hyperlinks, Web, Comments, INDIRECT, OFFSET

 
8. How to use the Excel Lookup Wizard (Lookup.xla) in Excel 2000 This step-by-step article shows you how to use the Lookup Wizard (Lookup.xla) in Excel 2000. The Lookup Wizard is part of a series of add-in wizards that Microsoft has made available to enhance your use of Microsoft Excel. The Lookup Wizard helps... KB#214252. Keywords: Add-In, SERIES, Formulas, Files, INDEX, MATCH

 
9. Validate / Validation Keywords: Format, Errors, Formulas, Sounds, Defined Name, VBA, VBE, Web, Comments, Validation, LEN, ROUND, VLOOKUP, COUNTIF

 
10. Excel Filters -- Advanced Filter Introduction 1. Set up the database For a workbook with sample data and criteria, click here. 2. Set up the Criteria Range (optional) In the criteria range, you can set the rules for the data that should remain visible after the filter is applied. You can use one criterion, or several. After the... Keywords: Advanced Filter, Criteria, Wildcard

 
11. How to Use the LOOKUP Function This article describes how to use the LOOKUP function in a Microsoft Excel 2002 worksheet or in a Microsoft Office Excel 2003... KB#324986. Keywords: Arrays, Errors, LOOKUP, HLOOKUP, VLOOKUP

 
12. How to AutoFilter Records Based on Cell Formatting This article describes how to use the AutoFilter feature of Microsoft Excel to filter records in a database where a particular field has been formatted to a bold... KB#151449. Keywords: AutoFilter, Colors, Fonts, Format, Formulas, Defined Name

 
13. Excel -- Data Validation -- AdvancedTechniques Tips Use Dynamic Lists Data Validation Dropdowns and Change Events Data Validation Dropdowns and Freeze Panes Make the Dropdown List Temporarily Wider Make the Dropdown List Appear Larger -- Zoom in when specific cell is selected -- Zoom in when specific cells are selected -- Zoom in when any cell... Keywords: Criteria, Download, Fonts, Errors, Formulas, VBA, Validation, Window, MATCH

 
14. Excel -- Data Validation -- Dependent Lists Create Dependent Lists You can limit the choices in a Data Validation list, by using named ranges and the INDIRECT function. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list. Create Named Lists Start by creating Named Lists, which will be the... Keywords: Criteria, Download, Formulas, Defined Name, Validation, INDIRECT, OFFSET

 
15. How to autoFilter Records Based on Cell Formatting in Excel 2000 This step-by-step article describes how to use the AutoFilter feature of Microsoft Excel to filter records in a database based on cell formatting in certain fields. Filter Based on a Bold Font Start Microsoft Excel, and then type the following data... KB#213923. Keywords: AutoFilter, Colors, Fonts, Format, Formulas, Defined Name

 
16. Index and Match instead of VLOOKUP? If you've been reading Excel tips for a while, you have invariably found someone who talks about using INDEX() and MATCH() instead of VLOOKUP. Speaking for myself, it was always too hard to try and master TWO new functions simultaneously. But, it IS a cool trick. Give me five minutes and I will try... Keywords: Formulas, VBA, Web, INDEX, MATCH, VLOOKUP, IS

 
17. How to perform a TwoDimensional Lookup in Excel 2000 This step-by-step article shows you how to use Microsoft Excel 2000 to produce two-dimensional lookups in a worksheet. A two-dimensional lookup is a method of finding data with two arguments. The two arguments in an Excel worksheet are typically a... KB#275170. Keywords: Format, Arrays, Formulas, Natural language, INDEX, MATCH

 
18. How to rank Duplicate Values Sequentially in Excel 2000 This step-by-step article describes a formula that you can use to assign a unique rank for all numbers in a range, even if the range includes duplicate values. If a row or column of cells in a Microsoft Excel worksheet contains duplicate values,... KB#213916. Keywords: Rank, Arrays, Formulas, OFFSET

 
19. How to Rank Duplicate Values Sequentially If a row or column of cells contains duplicate values, the RANK function assigns the same rank value to every occurrence of the duplicate value. This article describes a formula that you can use to assign a unique rank for all numbers in a range,... KB#152567. Keywords: Rank, Arrays, Formulas, OFFSET

 
20. How to Perform a CaseSensitive Lookup You can use LOOKUP functions in Microsoft Excel 97 and later to compare values to find an exact match, but the match is not case-sensitive. However, you can combine the LOOKUP functions with other built-in functions to perform a case-sensitive... KB#214264. Keywords: Formulas, LOOKUP, EXACT, HLOOKUP, VLOOKUP

 
21. Performing a Lookup with Unsorted Data in Excel In Microsoft Excel, the LOOKUP worksheet function has a vector form and an array form. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row... KB#181212. Keywords: Sorting, Arrays, Formulas, LOOKUP, VLOOKUP, INDEX, MATCH, OFFSET, HLOOKUP

 
22. How to Use the VLOOKUP Function The VLOOKUP function searches for a value in the leftmost column of a table, and then returns a value in the same row from a column that you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column... KB#324985. Keywords: Sorting, Errors, VLOOKUP, HLOOKUP

 
23. How to eliminate redundant data in an Excel list "Stuck in NJ" asked this week's question. I am creating a workbook that contains charts of sales figures. The first worksheet corresponds to the first sales territory in the company (eg. FL01.) The worksheet's tab name (as well as cell A1) is the sales territory number FL01. The data that drives... Keywords: Charts, Sales, Format, Errors, Printing, VBA, Macro, Web, VLOOKUP

 
24. How to Use INDEX and MATCH Worksheet Functions with Multiple Criteria This article offers several examples that use the INDEX and MATCH worksheet functions in Microsoft Excel to find a value based upon multiple... KB#214142. Keywords: Criteria, Arrays, Formulas, INDEX, MATCH

 
25. Excel drop down lists If you wish to present a user with a restricted choice of options and don't want to risk an item being miss-typed in a cell, drop down lists are an ideal solution. These lists display all of the available choices to the user so that they can click on their preference. Excel allows you to place two... Keywords: Format, Outline, Formulas, Combo Box, Object, Toolbar, Hyperlinks, Validation, MATCH, OFFSET

 
26. Random Numbers The usual request for Random numbers is how to generate random numbers without duplicates. While these may no longer be random numbers it is a frequent request. The following will work for small sets. It might be better to do something else for larger sets because checking every previous numbers... Keywords: Sorting, Arrays, VBA, Macro, Web, Comments, CELL, OFFSET, TODAY, INDIRECT, NOW, INFO, RAND, RANDBETWEEN

 
27. Excel Filters -- AutoFilter Basics AutoFilter Basics Prepare the Database Filter the Database Remove a Filter Create a Custom Filter 1. Set up the database a) The first row (A1:G1) has headings. b) Subsequent rows contain data. c) There are no blank rows within the database. For a workbook with sample data, click here. d) There is a... Keywords: AutoFilter, Criteria, Sales, Printing

 
28. How to Use the INDIRECT Function to Create References In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. The INDIRECT worksheet function can be used to create linked references to other workbooks. Each attribute of the... KB#151323. Keywords: Errors, Formulas, Links, INDIRECT

 
29. Excel -- Data Validation -- Hide Previous Selections Hide Previously Used Items in Dropdown Thanks to Excel MVP, Peo Sjoblom, for the brilliant array formula that makes this work! Set up the Main Table Create the List of Items Create the Validation List Apply the Data Validation Test the Data Validation You can limit the choices in a Data Validation... Keywords: Criteria, Download, Arrays, Formulas, Validation, INDIRECT, OFFSET

 
30. Help using Excel spreadsheet functions - vlookup, isna, match The Vlookup function is commonly used to get details from a table of data. A specified value is looked for in the first column and a value from an adjacent column is returned. =Vlookup(SearchItem, TableRange, ColumnNo, Exact). For example you may search for a surname in a list and then return a... Keywords: Errors, Formulas, VLOOKUP

 
31. Calculation Problems When Custom Function Creates a Defined Name When you press ENTER after typing a function into a worksheet, the worksheet may not be calculated correctly, and the message Calculate may remain in the status bar, instead of disappearing when you press... KB#248179. Keywords: Defined Name, TODAY, RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW

 
32. Excel -- Data Validation -- Use List from Another Workbook Use a List from Another Workbook Create the Source List Create a Reference to the Source List Create the Dropdown List Create a Dynamic Range from Another Workbook You can use a list from another workbook as the source for a Data Validation dropdown list. For data validation to work, the workbook... Keywords: Criteria, Errors, Formulas, Validation, OFFSET

 
33. How to Return the First or Last Match in an Array You can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it returns the last... KB#214069. Keywords: Arrays, LOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH

 
34. Changing Cell References MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: Formulas, VBA, Links, Web, ROW, ADDRESS, INDIRECT

 
35. How to create a Link to a Worksheet within an Excel WorkBook in FrontPage 2000 This article describes how to link a web page to a particular worksheet in an Excel workbook.Create a Link to a Specific Worksheet When you create a link to an Excel workbook, it will open to the last worksheet that was viewed. To create a link to... KB#229808. Keywords: Links, Files, Hyperlinks, Web

 
36. Help using Excel spreadsheet functions - Offset, Choose These functions are both very useful for creating worksheets which allow users to create alternative reports - for example by choosing a different month number. Offset returns a value from a cell which is at a user defined point in your spreadsheet and is in the format: =OFFSET(BaseRef, Rows, Cols,... Keywords: Charts, Errors, Formulas, OFFSET, CHOOSE

 
37. Excel -- Pivot Tables -- Dynamic Data Source Dynamic Data Source 1. Pivot Tables -- Introduction Getting Started Use a Dynamic Data Source 2. Pivot Tables -- Data Field Layout 3. Pivot Tables -- Show and Hide Items 4. Pivot Tables -- Clear Old Items Many books and web sites have information on creating Pivot Tables. For a brief introduction,... Keywords: Download, Formulas, PivotTables, PivotChart, Web, OFFSET, COUNTA

 
38. Excel -- Pivot Tables -- Grouping Data Grouping Data In a Pivot Table, you can group the items in a Row or Column field. For example, items in a date field can be grouped by month, and items in a number field can be grouped by tens. Grouping Dates To group the items in a Date field Grouping Dates by Week To group the items in a Date... Keywords: Dates, Days, Download, Errors, Error Message

 
39. Miscellaneous Code fragments of Worksheet functions Rows & Columns Most of the following example show obtaining the value located in Column D for the row specified in cell A1. Another example shows obtaining the column letter for a specified cell. =GetFormula() was used to show the formulas actually used. The MarkCells macro was used to place the... Keywords: Formulas, Macro, Web, Comments, INDIRECT, OFFSET, INDEX, CELL, MID, ADDRESS, VLOOKUP

 
40. Perform two-way table lookups All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine the tax rate for a given income amount. The lookup functions in... Keywords: Charts, Sales, Format, Formulas, Printing, Natural language, VLOOKUP, INDEX, MATCH

 
41. Make a Drop Down List Perhaps one of the most requested items is the way to make a drop-down list so that one can request an item that magically pops into a cell. That in in turn be used as a value for a lookup or almost anything. Though this is a useful tool, it would be impractical to cover a complete column with drop... Keywords: Printing, Toolbar, Links, INDEX

 
42. How to calculate and display the largest value try with this one instead:=INDEX(C:C,MATCH(MAX(Q:Q),Q:Q,0)) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.... Keywords: Formulas, VBA, Web, INDEX

 
43. Use Index and Match to lookup a cell to the left of your key cell “14 20 12 25 10 35 8 50 4 65 I have a cell at let say E5 = to 30 the next number up is 35 and i want to display the 10. If E5 = to 55 than i want it to display 4 What is the formula for this?” If the data is located in A2:B6, then use this formula:=INDEX($A$2:$A$6,MATCH(E5,$B$2:$B$6)+1) MrExcel.com... Keywords: Formulas, VBA, Web, INDEX

 
44. Excel -- Named Ranges Name Box Create a Dynamic Range You can use a dynamic formula to define a range. As new items are added, the range will automatically... Keywords: Formulas, Defined Name, OFFSET, COUNTA

 
45. Alternate row shading using Conditional Formatting One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task. Select the range that you want to format Choose Format, Conditional Formatting In the Conditional Formatting... Keywords: Charts, Colors, Format, Patterns, Formulas, Printing, MOD

 

<< (Page 1)  Previous              Next  (Page 1) >>