return Cell address based on value

[basel_faulty]

hi all,i know this is gonna be simple but i'm turning myself in circles here,so thought i'd throw myselfto the mercy of the forumi have a basic tale of data that will change daily (its made up of 2 sets of rankings)4 8 4 3 4 7 4 46 6 6 2 6 5 6 18 4 8 3 8 5 8 15 5 5 2 5 7 5 17 8 7 2 7 5 7 13 5 3 2 3 4 3 12 4 2 3 2 6 2 11 7 1 3 1 4 1 1I'm trying to locate and identify the cell that returns the cell ranking "1 1", as this will then drive a vlookup or offsetI've searched the forums can find things such as CELL("address"that look like they should work but to no avail,any ideas chaps ?

 

[Joe4]

Not sure what kind of device you are posting from, but your post (including your data example), came out as one long jumbled string.
I think we need to see what you data actually looks (how it is structured) like to make sense of it.

Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[basel_faulty]

sorry for that,
try again, i have a basic set of data that changes daily (as per example, each row of data has a ranking (1 to 5) and each cell a ranking within that relevant row)

what i am trying to do is get a formula that locates the cell in the table with the value "1 1",

 

[Joe4]

Is everything in column A?

 

[basel_faulty]

hi Joe4,
Column A has the primary ranking detail, but the "scores" are grouoed in that 5x5 table.to the right of that i have prefilled text boxes, this is why i'm trying to get the cell location so i can then vlookup to get the text.
hope this makes sense , i cant post the actual spreadsheet as we now cant log on from work (see the terrible formatting above) , so i'm having to do this from home and then mock up a version on my own PC.

 

[Rick Rothstein]

Does this array-entered** formula do what you want...

=ADDRESS(MAX(IF(A1=B4:F8,ROW(B4:F8))),MAX(IF(A1=B4:F8,COLUMN(B4:F8))),4)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[basel_faulty]

Does this array-entered** formula do what you want...

=ADDRESS(MAX(IF(A1=B4:F8,ROW(B4:F8))),MAX(IF(A1=B4:F8,COLUMN(B4:F8))),4)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

worked like a dream thanks