Help with a combined INDEX() and MATCH() formula

[TheRiddler]

Hello guys,

Last night I've run across an error with a formula in my table

Let me put the table:

Now let me explain a little bit:
- ColumnI and ColumnJ in some cases they have no value! (like J5, J10 or J13 in my table)
- Usually these 2 columns have the following 3 possible values: blank (or none), 0 and >0 (above 0)

I've tried to use the following formula to extract the data from one column (exemple ColumnJ) on another sheet:

=INDEX(J2:J15,MATCH("Agent 4",E2:E15,0),1)

Instead of returning a blank value, I get in the cell the value 0!

If I use the IFERROR() around the formula itself:

=IFERROR(INDEX(J2:J15,MATCH("Agent 4",E2:E15,0),1), "N/A")

Instead of returning the "N/A" value in the cell that I'm calling this formula I still get the value 0!

Basically what I want is the following:
- return value "N/A" if the cell match in ColumnJ is blank
- return value of the cell (doesn't matter if it is 0 or above 0 - like 100)

Any ideas on to work this out ?

Regards,
Riddler

 

[id107]

This should work:

=IF(INDEX(J2:J15,MATCH("Agent 4",E2:E15,0),1)="","N/A",INDEX(J2:J15,MATCH("Agent 4",E2:E15,0),1))

 

[TheRiddler]

Perfect !!!

Thank you id107 for the formula. Works like a charm