BruceyBonu$
Board Regular
- Joined
- Mar 6, 2006
- Messages
- 74
yesterday someone helped me out with this great formula 
;
= sumproduct(("range array"="value to be located")*(Row("rangearray"))
= sumproduct(("range array"="value to be located")*(Column("rangearray"))
it locates the "value to be located" in the "range array" and returns the column and row reference, allowing me to lookup across multi column arrays.
Only one problem, where there are multiple iterations of "value to be located" in "Range Array", it adds them all up to give and invalid reference.
Can anyone amend the above formula to return the unique row and column references of the first or last instance it finds ?
;= sumproduct(("range array"="value to be located")*(Row("rangearray"))
= sumproduct(("range array"="value to be located")*(Column("rangearray"))
it locates the "value to be located" in the "range array" and returns the column and row reference, allowing me to lookup across multi column arrays.
Only one problem, where there are multiple iterations of "value to be located" in "Range Array", it adds them all up to give and invalid reference.
Can anyone amend the above formula to return the unique row and column references of the first or last instance it finds ?
