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

Lookup and Reference Examples

  • How to use Excel FORMULATEXT function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel MATCH Function
  • How to retrieve first match between two ranges in Excel
  • How to use Excel VLOOKUP Function

Data Analysis Examples

  • Filter Data Based on Date in Excel
  • How to Create Area Chart in Excel
  • Create Scatter Chart in Excel
  • Understanding Anova in Excel
  • Subtotal function in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number

Get address of lookup result in Excel

by

This tutorial shows how to Get address of lookup result in Excel using the example below;

Formula

=CELL("address",INDEX(range,row,col))

Explanation

To get the address of a lookup result derived with INDEX, you can use the CELL function.

Worked Example:   Basic text sort formula in Excel

In the example shown, the formula in cell G8 is:

=CELL("address",INDEX(B5:D11,MATCH(G6,B5:B11,0),2))

Which returns an address of $C$8, the address of the cell returned by INDEX.

Worked Example:   Match next highest value in Excel

How this formula works

Although INDEX normally displays the value of a cell at a given index, underneath it actually returns a reference.

Worked Example:   INDEX and MATCH with multiple criteria in Excel

By wrapping INDEX in the ADDRESS function, you can see the address of the cell returned by the lookup.

Post navigation

Previous Post:

Get cell content at given row and column in Excel

Next Post:

Get employee information with VLOOKUP 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
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • How to extract domain from email address in Excel
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • Extract most frequently occurring text in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • List holidays between two dates in Excel
  • Display Days until expiration date in Excel
  • Convert date to month and year in Excel
  • Convert decimal seconds to Excel time
  • Calculate expiration date in Excel
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • Example of payment for annuity in Excel
  • Calculate periods for annuity in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles