asked on

Excel Formula Help

Hello I have this formula listed below, it analyzes J5 to M5 to find a common value, but I discovered an error, here is the data this time listed below, the formula should return a -TSRA but it did not. any help would be greatly appreciated.

J5 K5 L5 M5
+TSRA -TSRA -TSRA

=IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(J5:M5,MODE(MATCH(SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),0))),"-",""),"+",""),M5)

barry houdini

Hello sandramac, that looks like one of mine.....

It needs to be confirmed with CTRL+SHIFT+ENTER and then it should return the value TSRA (it ignores + and minus signs....) what does it return, what do you expect?

regards, barry

sandramac

ASKER

Hi barry, the CTR-SHFT-ENTER worked, had a brain lapse on it. but on one instance it wont return the right data when the following is present:

J5 K5 L5 M5
TSRA -TSRA it should return -TSRA or TSRA, but it gives me a blank...

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

the way the formula is set up, it will never return a + or a - since these are substituted with "".

Can you explain what you want to achieve? Maybe there's a different approach.

cheers, teylyn

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sandramac

ASKER

Hello I have results from 4 different sheets that are displayed in J5 to M5 if there are two or more of the same value, ignoring any + or - signs then the return result will be that matching result with a + sign if there is one or a - sign if there is one, if both exist then use the + sign here are some examples below

J5 K5 L5 M5
RA +RA -RA return +RA
SHRA -SHRA -SHRA return -SHRA
SN +SN SN return SN
TSRA -TSRA return -TSRA

sandramac

ASKER

barry,

that did the trick.