Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to look up a value and return the cell address in Excel?
MS Excel is one the important tool of the MS Office suite. MS Excel is the best tool to interpret and analyze the large voluminous of data. In this article, the user will learn how to look up a value and return the cell address in Excel. This article demonstrates a simple example, by providing a data value, and looking at it from the provided table range, and finally printing the cell reference to the cell. The user define formula is used in the example to achieve the required result
Example 1: To look up a value and return the cell address in an Excel sheet, by using the user-defined formula.
Step 1
Consider an Excel spreadsheet. Here, create two columns, the first column name is the sample name, and the second column name is the number. After that go to the G4 cell and create a header for the value that needs to be checked. Then go to the G5 cell and write the data as “address”. Consider below given a snapshot of data −
Step 2
Go to the H4 cell, and type “D”. in the provided example, will try to evaluate the cell reference or address location of the cell that contains the sample name as “D”. consider snapshot of below provided image −
Step 3
Go to the H5 cell, and type formula “=CELL("address",INDEX(C3:C11,MATCH(H4,C3:C11,1)))”. Screenshot for the same is provided below −
Explanation for the formula:
The formula =CELL("address",INDEX(C3:C11,MATCH(H4,C3:C11,1))) in Excel is used to determine the address of a cell that contains a specific value within a range.
Let's break down the formula:
INDEX(C3:C11,MATCH(H4,C3:C11,1)) searches for the value in cell H4 within the range C3:C11 and returns the matched value.
CELL("address", ...) is a function that retrieves the address of a cell based on a given reference.
By combining these elements, the formula finds the cell in the range C3:C11 that contains the value matched with H4 and retrieves its address.
For example, if the matched value is found in cell C7, the formula will return the address "C7"
Step 4
Press the “Enter” key to obtain the required result as depicted in below image.
Conclusion
Being able to look up a value and return the cell address in Excel empowers users to navigate and extract relevant information from your datasets more effectively. With the formula CELL("address",INDEX(C3:C11,MATCH(H4,C3:C11,1))), user can streamline the data analysis processes, enhance decision-making, and create more efficient Excel solutions. By harnessing the power of Excel's functions, users can save time and work more efficiently with user data.
2K+ Views
