Lookup value in range

[kazphilips]

I am trying to find the location of a value in a range, however there is no beginning value in the range which I can use as a vlookup. In other words, in one worksheet I have a column of values. I need to find where they appear within a range on another worksheet. Please see attached.

Thanks!

[JieJenn]

Not smart to figure it out using normal function. Would VBA do?

Please Login or Register  to view this content.

[Ace_XL]

Try

=ADDRESS(SUMPRODUCT(($A$2:$H$15=A22)*ROW($A$2:$A$15)),SUMPRODUCT(($A$2:$H$15=A22)*COLUMN($A$2:$H$2)),4)

[kazphilips]

I thought it would be much simpler I really don't understand this formula, but it works!
Thank you Ace_XL!!