Thursday, 06 December 2018
  0 Replies
  2.5K Visits
0
Votes
Undo
Hi,

How am I able to retrieve the row and column number for a cell value which I do not know the column or row in which it exists? Is this possible? I have looked everywhere, but can't seem to find anything building on the index and match functions which don't help in this instance. A lot of the data I work with is all jumbled, have no row or column headings, and besides using CTRL F to find what I am looking for, I can't seem to figure out a way where I can search for a value within multiple columns and rows. Also, I am trying to work this out without the use of macros or additional add-ons for excel.

Attached is an image of a simple table for what I am trying to convey. Also below is the same data, however will probably lose its formatting once I post this topic

A B C D E F G H
1 aa ff kk pp uu qq
2 bb gg ll qq vv 2
3 cc hh mm rr ww 4
4 dd ii nn ss xx
5 ee jj oo tt yy

Say the raw data exists in the range A1:E5 (in reality I have 10s of thousands of rows and up to 100 columns of raw data). One cell within this range holds the value "qq", however I don't know which row or which column. If I now enter the value "qq" within Cell H1 (pretend Cell H1 is like a google search box), I want Cell H2 to return the row "qq" exists in, and Cell H3 to return the column "qq" exists in. Again, I don't know at all which row or column "qq" may belong in, hence I cannot use the index and match function as this function requires some prior knowledge of which row or column "qq" belongs in.

Is anyone able to assist?

Thanks - Marty
There are no replies made for this post yet.