Open post
Dumb Question Corner Logo

How to Find the Cell Reference for a Searched Value in an Array in Excel

excel logoJohn Ormsby (aka NASANUT) sent in what he playfully called a Dumb Question:

I have a spreadsheet that contains a 10 by 10 matrix of values. Each value is different. There are column headings at the top, ranging from 0 to 9 and there are row headings on the left, also ranging from 0 to 9.

What I want to do is this. I want to search for a value within the matrix, and if found, want Excel to tell me the row and column that it is in. In other words, if I search the matrix for ‘150’ and it is found at cell F7, then Excel can either tell me ‘F7’ or the row and column headings (I can work with either).

The problem I have is that all of my searching only results in information on how to go the other way (i.e. provide the column and row and ‘find’ the value).

The beautiful thing about this question is that a) it’s not at all dumb, it’s a surprisingly hard problem, and b) I had a lot of other things on my to do list (some of them fairly urgent) but of course I dropped everything to try to figure this out!

Continue reading “How to Find the Cell Reference for a Searched Value in an Array in Excel”

Scroll to top