Amend Formula

Seamus2626
Seamus2626 used Ask the Experts™
on
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))))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I dont kwow about fitting it directly into your exisiting formula but it sounds like you need to use ABS.

ABS will give the absolute number,  eg =ABS(-725) will be 725,  ABS(725) is 725, ABS(-12) is 12 etc

So looking at your formula again you might just need to put ABS( ) around the F2 within the MATCH?
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

ABS() is the way to go, but it needs to be in an array formula, since it will be applied to the lookup range for Match(). With Indirect(), this won't work. Indirect is never a good choice and can more often than not be replaced by Index().

The following works in my tests. Confirm with CTRL-Shift-Enter, since it is an array formula

=IF(ISNA(MATCH(F2,ABS(F3:F1000),0)),"",MATCH(F2,ABS(F3:F1000),0)+ROW()&IF(INDEX(J:J,MATCH(F2,ABS(F3:F1000),0)+ROW())="","",","&INDEX(J:J,MATCH(F2,ABS(F3:F1000),0)+ROW())))

cheers, teylyn

Author

Commented:
Excellent, thanks for the help guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial