Index formula not pulling information

[ClaraHam]

I have used the Index formula to pull information from another sheet within the same workbook, but it does not work properly. The 2 sheets are identical, and should populate the relevant information, but it isn't.

Formula:
=IFERROR(INDEX(Outstanding!I:I,MATCH(J2,Outstanding!J:J,0)),"")

I am either getting nothing, 0 or 0-Jan-00. I used the exact same formula in the adjacent cell, and all relevant information is pulling...I am at a loss.

Thanks!
Clara

 

[Gerald Higgins]

It works for me, as long as

a) J2 contains something
b) The value in J2 is also found in the range Outstanding!J:J
c) There is a value in the range Outstanding!I:I on the same row as the value of J2 in Outstanding!J:J

Are you sure all of these are true ?

 

[ClaraHam]

It works for me, as long as

a) J2 contains something
b) The value in J2 is also found in the range Outstanding!J:J
c) There is a value in the range Outstanding!I:I on the same row as the value of J2 in Outstanding!J:J

Are you sure all of these are true ?

To answer your question:
a) J2 does contain something - always
b) The value isn't for certain in the range J:J, it is just looking for matches
c) There isn't always a value in the outstanding range I:I on the same row, sometimes there is nothing.

Do you think that is why? The purpose is as follows:

1 sheet is a worksheet, sometimes the worksheet requires comments for certain items. Every day, new data is exported that may be something that has already been looked at and comments added - in that case, I want the formula to look at a value, and if it finds it - populates the comments where there are some. Make sense?

 

[Gerald Higgins]

Looking at this again . . .

If J2 is blank, the formula returns blank.
If J2 is NOT blank, but contains a value that is NOT found in "Outstanding!J:J", the formula returns blank.

So if your formula returns a blank, then the reason is one of the two above conditions applies.

If J2 is NOT blank, and contains a value that IS found in "Outstanding!J:J", but there is NO corresponding value in "Outstanding!I:I", the formula returns 0.

And finally, if J2 is NOT blank, and contains a value that IS found in "Outstanding!J:J", and there IS a corresponding value in "Outstanding!I:I", the formula returns that value from I:I.

 

[ClaraHam]

Looking at this again . . .

If J2 is blank, the formula returns blank.
If J2 is NOT blank, but contains a value that is NOT found in "Outstanding!J:J", the formula returns blank.

So if your formula returns a blank, then the reason is one of the two above conditions applies.

If J2 is NOT blank, and contains a value that IS found in "Outstanding!J:J", but there is NO corresponding value in "Outstanding!I:I", the formula returns 0.

And finally, if J2 is NOT blank, and contains a value that IS found in "Outstanding!J:J", and there IS a corresponding value in "Outstanding!I:I", the formula returns that value from I:I.

This is perfect! So if I don't want the "0" value to appear, I can use an IF formula that will return blank if the value is zero? I will try it.

Thank you!

 

[Gerald Higgins]

Yes that should probably work.

There are other ways round this as well.

For example, you could change the number format for the cell containing the formula, to a format that displays 0 as blank, or some other character, perhaps "-".
You'll have to decide for yourself whether this approach is appropriate to your situation.