Index Match to pull negative and positive data

[paradise2sr]

I have been using index match function for a quite long time.
What currently I have a problem is that,I have to pull in one column negative number and another only positive number by matching.Here,negative and positive number are in same column.

=Index($F$12:$F$257,match($E5,$G$12:$G$257,0))

Also,Reference,$E5 is blank then it will alter the reference to $C5.

[6StringJazzer]

You are using MATCH with the third argument 0. This means find an exact match. It doesn't matter if the number is negative or positive. We need a more complete explanation of what you are trying to do.

As for the second part

Please Login or Register  to view this content.

[paradise2sr]

Ok,plz find enclosed in attachment a sample data.I hope this will be suffice info.Currently there is #N/A error.
Results are required in column G,H which is highlighted with green.

[AliGW]

Your IF clause is incomplete:

=INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0))

[paradise2sr]

Thanx for pointing out.

Kindly let me know about pulling negative and positive value separately.I have tried but looking for a short one.

For positive column,

Please Login or Register  to view this content.

For Negative column

Please Login or Register  to view this content.

[AliGW]

Positive: =IF(INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0))>=0,INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0)),"")

Negative: =IF(INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0))<0,INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0)),"")

[AliGW]

Why do the formula need to be shorter? They work. Aesthetics don't matter.

[AliGW]

These will work:

=MAX(0,INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0)))

=MIN(0,INDEX(Data!$E$12:$E$16,MATCH(IF(Result!$E2="",Result!$B2,Result!$E2),Data!$F$12:$F$16,0)))

HOWEVER, you might then want to conditionally format the cells to hide the 0 values.

[AliGW]

Please Login or Register  to view this content.

This will change the negative value to a positive one - why are you doing this?

[jason.b75]

Have you upgraded to a newer version of excel than that shown on your forum profile? For excel 2019 / or 365, you could use

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

for positive and negative results respectively.

[paradise2sr]

Thanx all of you.My problem has now been solved and thanx for the consistent support.

[AliGW]

Great. Don't forget to mark the thread as solved.

[paradise2sr]

I have already done mam.Thanx for your kind help.