5

I'm trying to go to the first instance of a #N/A cell, the result of a VLookup that failed. I know I can conditionally change the value when the result is #N/A, but what I want to do is just locate the specific cell that failed.

Ruben Misrahi
  • 63
  • 1
  • 1
  • 6

2 Answers2

6

You can also just search for #N/A values. Highlight the column that you want to search, hit CTRL+F, and click on the Options >> button.

  • Find what: #N/A
  • Look in: Values

enter image description here

picobit
  • 321
  • 1
  • 10
5

Consider the following screenshot.

enter image description here

In row 4, the value 3 returns an #N/A value in column E. It's a simple Vlookup formula.

The helper column F determines whether or not column E has an N/A error with the fomula =IsNa(E2) in cell F2 and copied down.

Now you can use a formula to find the first N/A value, like in cell H2 with

=INDEX(D:D,MATCH(TRUE,F:F,0))

It will return the first value from column D that did not have a match but a N/A.

If that is not what you want to achieve, please post a sample file with data and the expected result.

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • I guess my question was much simpler. I didn't know you could find/search by entering #N/A (or some other errors such as #VALUE! in the Find dialog once you select Options there. Thanks teylyn. I guess your solution can be used for other more complicated situations. – Ruben Misrahi Jul 12 '16 at 12:29