How to Find the Position of a Value in Excel?
Find Value Position in List is a Excel function that finds the row number of a specified value in one column and returns the corresponding value from another column.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Discover how to locate a value's position in a list and use it to retrieve data from a different column effortlessly.
The Formula
"Find the position (row number) of a value in a list, then use that position to return a value from a different column"
=INDEX(B:B, MATCH(A1, A:A, 0))This formula finds the row number of a specified value in one column and returns the corresponding value from another column.
Step-by-Step Breakdown
- MATCH(A1, A:A, 0) finds the row number of the value in cell A1 within column A.
- INDEX(B:B, ...) uses the row number found by MATCH to retrieve the value from column B.
- The '0' in MATCH specifies that an exact match is required.
- This formula assumes that the data is in two columns, A and B.
Edge Cases & Warnings
- The value in A1 does not exist in column A.
- Column A or B contains blank cells.
- Multiple occurrences of the same value in column A.
- The data ranges are not of equal length.
Examples
"A1 = 'Apple', A:A = {'Banana', 'Apple', 'Cherry'}, B:B = {'Red', 'Green', 'Yellow'}"
'Green'"A1 = 'Cherry', A:A = {'Banana', 'Apple', 'Cherry'}, B:B = {'Red', 'Green', 'Yellow'}"
'Yellow'Frequently Asked Questions
What happens if the value is not found?
The formula will return an error (#N/A) if the value is not in the list.
Can I use this with non-contiguous ranges?
No, the ranges must be contiguous for the INDEX and MATCH functions to work correctly.
Is this formula case-sensitive?
No, the MATCH function is not case-sensitive by default.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.