Lookup cell references in excel

Samuel Steinhardt 20 Reputation points
2026-08-21T12:26:36.44+00:00

Hi,

I know how to use the VLOOKUP and HLOOKUP functions. These functions help find cell values which I need. But sometimes, I need the cell reference which contains the value. How do I do that? For example: suppose I have on a spreadsheet (silly example) all of the letters in the alphabet, each cell containing one letter (eg: a1="a", a2 = "b", a3 = "c", etc.), one thousand times. So if I want to obtain all the cells which contain the letter "x", I know how. But suppose I want to know every single cell reference which has the letter x, in other words I want a formula which shows me "T41", "Z102", "AB3060", for all those cells contain the letter "x", how do I do this?

Microsoft 365 and Office | Excel | For home | Windows
0 comments
Answer accepted by question author
Marcin Policht 106.8K Reputation points MVP Volunteer Moderator
2026-08-21T14:29:02.68+00:00

Yep - VLOOKUP and HLOOKUP return the value found - you want to return the address of the cell containing that value.

If your data is in A1:ZZ1000 and you want every cell containing "x", the easiest approach in modern Excel is FILTER combined with ADDRESS and ROW/COLUMN:

=FILTER(ADDRESS(ROW(A1:ZZ1000),COLUMN(A1:ZZ1000),4),A1:ZZ1000="x")

This will return a spilled list such as:

T41 Z102 AB306 ...

The ADDRESS function generates the cell reference for every cell in the range, while FILTER keeps only the references where the corresponding cell contains "x".

If you want to search the entire worksheet, you shouldn't use the entire worksheet as the range because that can be very large and inefficient. Instead, specify the actual area containing your data, such as A1:ZZ1000.

If you are using an older version of Excel that doesn't have FILTER, there are other formulas that can do this, but the formula is considerably more complicated.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Newest
Most helpful Newest Oldest
  1. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2026-09-06T00:13:36.17+00:00

    Hi,

    In cell I2, enter this formula

    =LET(a,B2:E12,FILTER(TOCOL(ADDRESS(ROW(a),COLUMN(a),4)),TOCOL(a)="a"))

    Hope this helps.

    User's image

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.