Remove #N/A Error from INDEX

[JTL9161]

I have the following INDEX formula:

=INDEX($AS$140:$AS$155,MATCH($AT158,$AT$140:$AT$155,0))

I am trying to have it return a "0" instead of #N/A.

I think it would be a IFERROR instead but cannot get it right.


Thanks,
James

 

[ThatOneDude]

Instead of IFERROR, try IFNA. That function allows you to determine what happens if the result is #N/A.

 

[Fluff]

How about

=ifna(INDEX($AS$140:$AS$155,MATCH($AT158,$AT$140:$AT$155,0)),0)

 

[JTL9161]

thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Scott Huish]

You have Excel 365. This should work for you:
=XLOOKUP($AT158,$AT$140:$AT$155,$AS$140:$AS$155,0)