• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Locating Values in Large Excel Tables

You are here: Home / Excel / Locating Values in Large Excel Tables
locating values in large excel tables
January 17, 2017 by Mynda Treacy

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals

The previous post on this topic included a method for finding the cell address for the maximum or minimum number in a table.

Thanks for the correspondence  --  and especially that from Hervé, Saskia and Trevor for the questions about what happens if there are multiple maximum or minimum values.

The previous method sometimes failed where there were multiple maximum or minimum values.

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

This version shows a different approach that works reliably under that situation too.

 
  1. The ‘index table’ method

Instead of setting up the reference row and column against the table as shown in the previous model (shown in light green here):

the index table method

We use a method that works with an ‘index table’ related to the table we are investigating (shown in blue below). And we set up the ‘index table’ (shown in green below) to have one row for each cell in the blue table.

In the index table:

Row 1, Col 1 refers to cell January, Wk1 in the main table

Row 1, Col 2 refers to cell January, Wk2 in the main table

Row 2, Col 3 refers to cell February, Wk3 in the main table

etc.

index table example

It may take a few moments to set up the index table, since it needs to have one row for every cell in the table we are investigating (and could be very long if we have a large number of rows and columns in the table we are investigating).

The aim is to get an entry in the index table for each cell in the original table.  In the index table column headed ‘Match’, we do a computation on whether the cell in the original table matches the Maximum (or Minimum if selected).

The formula for each cell in the Match column is:

=IF(INDEX(MyTable,[@Row],[@Col]+1)=Selected,TRUE,"")

This may look a bit complex, but it’s not too difficult if dissected, and it says in English:

Go to the blue table (MyTable). Using the INDEX function, go to the cell given by the Row and Column numbers in the green table.

But wait a minute . . . . how does the ‘+1’ come into the formula?

The first column of MyTable contains the month names, and since we are only interested in the numbers, we add one to the column so we can skip over the month names.  So that means we are considering the Wk1 column to be the first column, the Wk2 column to be the second column, whereas in the Excel table structure, the Month is considered to be the first column and Wk1 is the second column etc.

For each cell in the original table, the index table entry is TRUE if the cell matches the Maximum (or Minimum if selected) and blank if there is no match.

This leaves us with a TRUE value in the index table for every match.  It’s easy to see and verify, too.

 
  1. Getting the match information from the index table

Say this is our result after refreshing the worksheet:

number of matches in the index table

To find the number of matches, we count the number of times the value TRUE appears in the index table column ‘Match’:

=COUNTIF(IndexTable[Match],TRUE)

To find the table position of the first match, the row of the first match in the original table is given by the OFFSET function:

=OFFSET(IndexTable[[#Headers],[Row]],MATCH(TRUE,IndexTable[Match],0),0)

i.e. find the first value of TRUE down the index table (the MATCH function will provide the number of entries down the index table where the first match is located), then use the OFFSET function to count down that same number of rows from the top of the Row column in the index table.

Similarly to finding the row of the first match, the column of the first match in the original table is given by the OFFSET function:

=OFFSET(IndexTable[[#Headers],[Col]],MATCH(TRUE,IndexTable[Match],0),0)

i.e. find the first value of TRUE down the index table, then use the OFFSET function to count down that same number of rows from the top of the Col column in the index table.

Once the row and column of the first match are found, OFFSET can be used again to locate the first matched call in the original table, and to work out its cell address:

=CELL("address",OFFSET(MyTable[[#Headers],[Month]],$L$12,$L$13))
 
  1. Some neat conditional formatting

Mynda recently discussed using logical functions to drive conditional formatting, so in this example, I’ve extended that to differentiate between maximum values being found or minimum values being found.

The cell used to denote Maximum or Minimum has the range name ‘SelectType’

SelectType

So we can set conditional formatting in the original table to be driven by two logical conditions:

set conditional formatting

If SelectType is set to Minimum, then paint the minimum cells red:

set SelectType to minimum

paint minimum cells red

If SelectType is set to Maximum, then paint the maximum cells green:

set SelectType to maximum

paint maximum cells green

So, we’ve covered a more reliable way to locate the row number, column number and cell address largest and smallest numbers – plus some more effective conditional formatting for easily highlighting the locations of those numbers.

There’s a whole lot more on how to use these conditional formatting and formula techniques in the Excel for Customer Service Professionals course.

locating values in large excel tables

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: Excel
Previous Post:Finding a Needle in an Excel Haystack
Next Post:Return a Range from a UDFReturn a range from a UDF

Reader Interactions

Comments

  1. Harold

    January 18, 2017 at 6:28 pm

    Hi Roger,

    Thanks for the feedback.

    Yes, conditional formatting works a treat, and the use of conditional formatting is explained in the first article and demonstrated in the second sheet of the workbook.

    The challenge we set out to meet is how to get the cell address of one or more matching cells — as far as I know, something that’s not available with conditional formatting.

    Cheers,
    Harold

    Reply
    • Roger Govier

      January 18, 2017 at 8:29 pm

      But Harold, the point is that a simple copy of the table as I described, will allow you to determine where the values occur.
      I placed my copy table stating at U4, and called it Table2

      Then, the CF in Table 1 merely needs to be =U5=1

      Then, each of the cells in your Table1 will get highlighted Green to correspond with the pattern of 1’s in Table2

      Reply
      • Roger Govier

        January 20, 2017 at 8:50 pm

        Harold

        Let me apologise sincerely.
        Had I read the article thoroughly first time, instead of just skimming it, I would have realised it was little to do with Conditional Formatting, but about reading the cell address where the high value resided.

        Your method does that excellently, whereas my suggestion would not have given any such result.

        Please accept my apology.

        Reply
        • Harold

          January 21, 2017 at 2:06 pm

          Hi Roger,

          Thank you for the reply – perhaps the original article was a bit long . . . .

          In any case, as you noted, conditional formatting works well (and you don’t even need to do the table of 1’s and 0’s).

          However, you need to know the cell address if you want to do any further computation that relies on the address of the maximum or minimum.

          If you know the address and the row/column position in the table (computed in the sample workbook), you can use the OFFSET function to answer questions such as:

          In what month does the maximum value occur?
          In what week does the maximum value occur?
          etc.

          Cheers,
          Harold

          Reply
  2. Roger Govier

    January 17, 2017 at 9:50 pm

    Hi Mynda

    This does seem to be a bit of an overkill to achieve the result.
    Why wouldn’t you just copy the whole table, and paste it somewhere to the right of the source table then simply enter the formula
    =–(MyTable[@Wk1]=Selected) in Wk1 January of the new table and copy across and down.

    You would then have a matrix of 1’s and 0’s, with the 1’s representing where the max values occurred in your source table.
    If you wanted, a simple CF of Cell Value =1 formatted Green would show the data neatly.

    Far easier than constructing an index table and going through all those steps.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.