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.
2 Answers
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
Consider the following screenshot.
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.
1
