CODEWITHSUNDEEP
excel
Bricked
Bricked

Reputation: 115

IF(ISNA) not working correcdtly

The following formula works as expected. It either displays #N/A in the column or it displays a dollar amount extracted from the other Sheet:

=INDEX('Import Invoice Upload'!K:K, 
        MATCH(VALUE(A54), VALUE('Import Invoice Upload'!C:C), 0))

I am trying to modify it using IF(ISNA) so that I can display "" instead of #N/A.

The following formula does display "" when it should, but it does not display the dollar value when it should as the above formula does.

=IF(ISNA(INDEX('Import Invoice Upload'!K:K, 
   MATCH(VALUE(A22), VALUE('Import Invoice Upload'!C:C), 0))), "",
   INDEX('Import Invoice Upload'!K:K, 
          MATCH(VALUE(A22), VALUE('Import Invoice Upload'!C:C), 0)))

Please let me know what I am missing.

Upvotes: 1

Views: 1905

Answers (2)

Bricked
Bricked

Reputation: 115

Thank you, all for the suggestions. Ctrl-Shift-Enter was interesting. I need to explore that more because I could not get it to work. But the If Error put me on the right track with the following solution working for me:

=IF(ISERROR(INDEX('Import Invoice Upload'!K:K, MATCH(VALUE(A219), VALUE('Import Invoice Upload'!C:C), 0))),"",(INDEX('Import Invoice Upload'!K:K, MATCH(VALUE(A219), VALUE('Import Invoice Upload'!C:C), 0))))

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Your first formula must be array-entered (with Ctrl-Shift-Enter) in order to resolve the range with the Value() function of the Match.

When you wrap the formula into IF(ISNA(formula),"",formula), then you also must confirm it with Ctrl-Shift-Enter to work. After doing this, the formula you posted returns the expected result.

enter image description here

Upvotes: 1

Related Questions