Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Get nth match with INDEX / MATCH in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to reference named range different sheet in Excel

Data Analysis Examples

  • Conditional Formatting Rules in Excel
  • How To Compare Two Lists in Excel
  • Add Outline to Data in Excel
  • How to Create Thermometer Chart in Excel
  • Excel Line Chart

Data Validation Examples

  • Excel Data validation require unique number
  • How To Create Drop-down List in Excel
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Excel Data validation number multiple 100

Lookup entire column in Excel

by

This tutorial shows how to Lookup entire column in Excel  using the example below;

Formula

=INDEX(data,0,MATCH(value,headers,0))

Explanation

To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is:

=INDEX(C5:F8,0,MATCH(I5,C4:F4,0))

Note: this formula is embedded in the SUM function only to demonstrate that all values are correctly retrieved.

Worked Example:   Position of max value in list in Excel

How this formula works

The gist: use MATCH to identify the column index, then INDEX to retrieve the entire column by setting the row number to zero.

Working from the inside out, MATCH is used to get the column index like this:

MATCH(I5,C4:F4,0)

The lookup value “Q3” comes from H5, the array is the headers in C4:F4, and zero is used to force an exact match. The MATCH function returns 3 as a result, which is fed into the INDEX function as the column number.

Worked Example:   Excel MAX, MATCH and ADDRESS function illustration

Inside INDEX, the array is provided as the range C5:F8, and the column number is 3, as provided by MATCH. Row number is set to zero:

=INDEX(C5:F8,0,3)

This causes INDEX to return all 4 values in of the array as the final result, in an array like this:

{121250;109250;127250;145500}

In the example shown, the entire formula is wrapped in the SUM function, which can handle arrays natively. The SUM function returns a final result of 503,250.

Worked Example:   Count and Sum Functions in Excel

Processing with other functions

Once you retrieve an entire column of data, you can feed that column into functions like SUM, MAX, MIN, AVERAGE, LARGE, etc. for additional processing. For example, you could get the maximum value in a quarter like this:

=MAX(INDEX(C5:F8,0,MATCH(I5,C4:F4,0)))

Post navigation

Previous Post:

Lookup and sum column in Excel

Next Post:

Get location of value in 2D array in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract name from email address in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to strip numeric characters from cell in Excel
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to strip protocol and trailing slash from URL in Excel
  • Convert decimal seconds to Excel time
  • How to calculate future date say 6 months ahead in Excel
  • Assign points based on late time in Excel
  • Get month name from date in Excel
  • How to calculate quarter from date in Excel
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • Calculate cumulative loan principal payments in Excel
  • Calculate original loan amount in Excel
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles