index / match question

[alwayslearning45]

Hello
I have the following formula which returns a #NA value if the contents of A8 are not actually present in the data (pivot table). I'd like to refine the formula so that it returns a 0 number value (or no value at all) if A14 is not actually there to match.

=INDEX(Data!$B$2:$B$16,MATCH(Waitomo!A14,Data!$A$2:$A$16,0))

Thanks.

 

[alwayslearning45]

Sorry, I meant the contents of A14......

Hello
I have the following formula which returns a #NA value if the contents of A8 are not actually present in the data (pivot table). I'd like to refine the formula so that it returns a 0 number value (or no value at all) if A14 is not actually there to match.

=INDEX(Data!$B$2:$B$16,MATCH(Waitomo!A14,Data!$A$2:$A$16,0))

Thanks.

 

[spencerp237]

You can just put it inside the IFNA function.

=IFNA(INDEX(Data!$B$2:$B$16,MATCH(Waitomo!A14,Data!$A$2:$A$16,0)),0)

You can change that last 0 to whatever you want the cell to display if the Index/Match returns #NA . Use "" for blank.

 

[alwayslearning45]

Hi, I'm now getting a #NAME error. FYI, the contents of cell A14 in this instance is a name.

You can just put it inside the IFNA function.

=IFNA(INDEX(Data!$B$2:$B$16,MATCH(Waitomo!A14,Data!$A$2:$A$16,0)),0)

You can change that last 0 to whatever you want the cell to display if the Index/Match returns #NA . Use "" for blank.

 

[spencerp237]

Replace IFNA with IFERROR then.

=IFERROR(INDEX(Data!$B$2:$B$16,MATCH(Waitomo!A14,Data!$A$2:$A$16,0)),0)