2007 Formula: Trying to locate a numeric value in a square named range.

[Spielberg]

I have a list of values consisting of: 1.1, 1.2, 1.3, 1.4, 1.5, 2.1 2.2, 2.3, etc. up to 10.5. There's 50 values total in a list.

Those values are also randomly placed in a 25 x 25 range named "AnswerGrid".

All I'd like to do is return the ROW of that values' location in the 25 x 25 named range. (COLUMN isn't necessary unless part of the formula.)

Thought it would be easy. I spent HOURS trying to figure this out.

As always I try to figure it out myself first. Here's what I tried so far:

• HLOOKUP, VLOOKUP of course won't work for this application as I need ROW of the data, not the data itself.
• Using every combination of INDEX, MATCH, ROW, COLUMN, ADDRESS I could.
• In using INDEX and MATCH, no matter what I tried (value in quotes, without quotes, or even just a letter to test, returned #N.A. Every single time.
• FIND and SEARCH didn't work for me, since I'm not looking within a text string.

I'm sure I'm missing something So simple!

Thank you in advance to anyone who can help - you folks are always on point!

Mike

[SamT]

Put Code in Standard Module

Public Function GetRow(ValuetoFind)
Dim Tmp
Tmp = Range("AnswerGrid").Find(ValuetoFind).Row
If IsNumeric(Tmp) then
GetRow = Clng(Tmp)
Else
GetRow = "N/A"
End If
End Function

Excel Cell Formula: =GetRow(1.1) or =GetRow("1.1") or =GetRow(A1)

[Spielberg]

Mr. SamT, just have to say that was UNBELIEVABLE! Worked absolutely FLAWLESSLY!

I NEVER would have been able to figure that out for the life of me!

*THANK YOU!*

Mike

[Bob Phillips]

This array formula will also do it

=MIN(IF(AnswerGrid=1.1,ROW(AnswerGrid)-MIN(ROW(AnswerGrid))+1,""))

[Spielberg]

Bob, thank you for that awesome formula! Mr. SamT had supplied an amazing one as well and I went with that one. I did forget to mark this as solved, but thank you for that great reply!

MIke