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

2 Answers2

7

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
  • 330
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
  • 23,615