Looking up a value to return the cell reference

Hi,

I have a range of values in an array, i want to look up a value in that array and return the cell reference.

My array is from b2:f7, so i am looking for a formula to return the cell reference for a valid entry in this range. For example if f7=30, and in cell x20 i also have 30, what formula do i use to find the 30 in the range b2:f7 by reference to x20?

I've battled with this all day and looked at more sites than i care to think about. Lots of comments about cell address, match, Index etc but while i can get a formula to work for one row when the row changes the formula falls over.

Can anyone help me please?

Thanks

Peter

Try

=ADDRESS(SUMPRODUCT(($B$2:$F$7=X20)*(ROWS($B$2:$F$7)+1)),SUMPRODUCT(($B$2:$F$7=X20)*(COLUMNS($B$2:$F$7)+1)))

Then ...

=IFERROR(ADDRESS(SUMPRODUCT(($B$2:$F$7=X20)*(ROWS($B$2:$F$7)+1)),SUMPRODUCT(($B$2:$F$7=X20)*(COLUMNS($B$2:$F$7)+1))),"")

Hi,

Thanks for the quick response.

I have tried this and whatever value is in x20 it always returns the same cell reference. I have only tried for valid references.

Any other ideas?

Thanks

Peter

Try this ...

=ADDRESS(SUMPRODUCT(($B$2:$F$7=X20)*ROW($B$2:$F$7)),SUMPRODUCT(($B$2:$F$7=X20)*COLUMN($B$2:$F$7)))

Thank you, thank you, thank you 100 times. I spent ages looking at this and you guys have fixed it for me.

Amazing!

Peter

Sorry about my "typo" re ROWS and ROW! Thank you Phuocam!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.