Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Amend Formula

Hi,

I have some formula that checks a cell, looks through a range and then returns the cell reference of any matching values.

I want a slight extension on this, so that it returns the cell reference of a value if it is the opposite direction as well

So if it was looking at 725 and in the range there was a calue -725, currently it would not return it is looking for 725, can i get the formula to look for 725 and -725?

Thanks
Seamus

=IF(ISNA(MATCH(F2,F3:F1000,0)),"",MATCH(F2,F3:F1000,0)+ROW()&IF(INDIRECT(ADDRESS(MATCH(F2,F3:F1000,0)+ROW(),10))="","",","&INDIRECT(ADDRESS(MATCH(F2,F3:F1000,0)+ROW(),10))))
SOLUTION
Avatar of Alan_White
Alan_White
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626

ASKER

Excellent, thanks for the help guys!