• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to retrieve the #N/A Value ?

G

GN0001

Member
I have this data in sheet 1:


John 4

John 2

Mary 5

Guity 1

Heidi 3

Guity 6


I have this data in sheet 2:

Guity 1 f

John 2 g

Heidi 3 h

John 7 N

Mary 5 k

Guity 6 L


I used this array formula:

INDEX($B$8:$D$13,MATCH(1,($B$8:$B$13=Sheet1!$B8)*(Sheet2!$C$8:$C$13=Sheet1!$C8),0),3)


I get these values:

#N/A

g

k

f

h

L

I


I need to find out what value is that returns me #N/A. It is a reverse order, I have a #N/A and I need to know what value is not matching.


I am very grateful to your help.

Guity
 
Guity,


Not very sure if this is what is required... but below are my 2 cents...


=IF(ISNA(INDEX($B$8:$D$13,MATCH(1,($B$8:$B$13=Sheet1!$B8)*(Sheet2!$C$8:$C$13=Sheet1!$C8),0),3)),Sheet1!$B8&Sheet1!$C8,INDEX($B$8:$D$13,MATCH(1,($B$8:$B$13=Sheet1!$B8)*(Sheet2!$C$8:$C$13=Sheet1!$C8),0),3))


~Vijay

sharma.vijay1@gmail.com
 
Vijay,


Can you please explain it what you have done? I partially understand. Imagine, we have new members in our group and they come up as #N/A. I want to know what these Look Up values are. I am working on a spread sheet that I get 500 #N/A and they are all new members or new values, I have data in more than 100K + Row. Please show me a way to locate those #N/A Values.


Regards,

Guity
 
Okay... the basic formala remains the same as per your original post...


the only difference is, when there is no value found we will get a #N/A..


In the first part of the formula I am checking if I am getting a #N/A for the current value and then displaying the corresponding Sheet1!$B8&Sheet1!$C8 values...


So Instead of a #N/A now; we are displaying the actual values of the entity which is not in Sheet 1.


Please try the below formula and let us know the results....


=IF(ISNA(INDEX($B$8:$D$13,MATCH(1,($B$8:$B$13=Sheet1!$B8)*(Sheet2!$C$8:$C$13=Sheet1!$C8),0),3)),Sheet1!$B8&Sheet1!$C8&"This is not is sheet1",INDEX($B$8:$D$13,MATCH(1,($B$8:$B$13=Sheet1!$B8)*(Sheet2!$C$8:$C$13=Sheet1!$C8),0),3))


~Vijay

sharma.vijay1@gmail.com
 
Vijay,

I am sorry, I can't understand. Let me start over with a function like VLookUp, which is easy.


In sheet one I have:


a

b

c

d

N

M

G

L


In sheet 2 I have:


a 1

b 2

c 3

d 4

N 5


This is the answer after doing Vlookup.


1

2

3

4

5

#N/A

#N/A

#N/A


I get 3 #N/A, because I don't have M, G, L in sheet 2. I have a case that I have lots of Rows and I get a lot of #N/A. I want to know to what Look Up value they are back to. I had this problem in the past and still I can't clearly think of a solution. Please solve this problem with these values. Thanks a lot.

Guity
 
Guity,


Please use the below formula in sheet 1 and let us know the results.


=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),Sheet1!A1,VLOOKUP(A1,Sheet2!A:B,2,0))


~Vijay

sharma.vijay1@gmail.com
 
Guity,

You can use vijay's formula if you're wanting to prevent the errors at the source formula, or you can use this one to "back-trace" where the errors are appearing:

=IF(COUNTIF(B:B,NA())<ROWS($A$1:A1),"",INDEX(A:A,SMALL(IF(ISNA(B$1:B$100),ROW(B$1:B$100)),ROW(A1))))


Note that the only range you should need to adjust in size is the B$1:B$100. Copy this formula down as far as you think would be necessary to display all the errors.
 
Hi Luke,

I will use what he says if I don't come up with any problem while I am using it.

Can you explain this part?

SMALL(IF(ISNA(B$1:B$100),ROW(B$1:B$100)),ROW(A1))))

How do you interpret this part for row part of Index function?

Regards,

Guity
 
Hi Guity,

I should have stated that it was an array formula to begin with. Anyway, the section of formula in question looks at B1:B100 and searchs for the #N/A error. If found, it stores an array of the row numbers. Since the cell can only display one of those values, we need some way to handle this. The SMALL function is there to take the n-th smallest function (note that the ROW(A1) section is there simply to increment the value as you copy down). So, it takes the first smallest row that contained #N/A, then the 2nd smallest, then 3rd, etc. It then combines that with the INDEX function to get the value corresponding to the #N/A.
 
Hi Luke,


This is very good answer, but I have to think about it, I understand and I don't understand, I am not very comfortable with it yet.


Which statement is "The second if"?

If(Condition, True, False) Or If(Condition, Value of the error)


Can you please send me a table with some values and apply this in cells to bring back the values that we can not find a match for.


Thanks

Guity
 
In "laymans" terms, the whole formula is:

=IF(Have I accounted for all the possible NAs yet?,TRUE - display blank, False - Take

the *next smallest row* that contained an NA value and plug it into index)


*This section is the "2nd If", which says:

IF(Does column B have NA?,True - Give me the row number, False - omitted/ignore)
 
Luke,

I have been exploring what you sent me.


I made a small data table and I broke the formula to better understand and then join parts.


This is what I have in sheet one:


This is my data in sheet 1:

John

Jack

Mary

Guity

Heidi

ford

paul

Ray


This is my data in sheet 2:

Guity 1

John 2

Heidi 3

John 7

Mary 5

Layla 6


This is the result of VLookUp:

2

#N/A

5

1

3

#N/A

#N/A

#N/A


I enter this formula on the VLookUp result:


IF(ISNA($G$8:$G$15),ROW($G$8:$G$15),ROW($G9)) and hit formula evaluation: it gives me

This:

The condition test: {false, true, false, false, false, true, true, true}

The true part when the condition is met: {8;9;10;11;12;13;14;15}

The false part: {9}

The formula result: {9;9;9;9;13;14;15} when I go to edit mode

The formula result when I go to formula evaluation is 9


My first question is to explain what happens here. which value against which one is compared?


Please answer this part then I bring my second question. Since I typed everything once, all of a sudden everything was disappeared and gone so I send this question not to exprience the failure again.


Regards,

Guity
 
There should be no False arguement, as we don't want to do anything with the non-NA values:

=IF(ISNA($G$8:$G$15),ROW($G$8:$G$15))


When you evaluate this formula, you should see:

{false;9;false;false;false;13;14;15}

Now, with no other functions in the formula, XL will just take the first argeument (in this case, FALSE) as the final formula result.


In the original formula, the SMALL function would pick out the smallest number (9), and pass that onto the next part of the formula.
 
Luke,

It is getting more clear.....


{false, true, false, false, false, true, true, true}

{8;9;10;11;12;13;14;15}


Can we say that each element in first array is compared to first element in second array in this way:

false against 8/ for this part, the final array shows false? because it says if there is a N/A element, grab the row number. But for the first element, we don't have N/A, so it is false, then the result array shows false for the first element.

--------------------------------------

true against 9 (I understand this part which says if it is true (there is a N/A, grab the row number,the pertaining element of the array, it returns 9)

--------------------------------------

and so on


Is the way how I analyze it right?


I think it would be all clear for me after I get my last response.


Thank you for all your help. It was great.

Guity
 
That sounds correct. Once the IF functions determines the initial true/false result for each cell in first array: ISNA($G$8:$G$15)

and gets this:

{false;true;false;false;false;true;true;true}


it then starts to fill in those slots with the appropriate choice from either the true "bin:

{8;9;10;11;12;13;14;15}

or the false bin (which is nothing, thus defaults to false)

{false;false;false;false;false;false;false;false}


This results in outcome:

{false;9;false;false;false;13;14;15}
 
but we don't have false part at all, Row(A1) is nth of array for small function.

if we have that part, then we have

false, 8 , false

true, 9, false


The first one results into 0, if we consider that we don't have false part, then we have true and 9 or 1 and 9 which give us 9 in this array: {false;9;false;false,13;14;15}


I hope I am clear.

Guity
 
I'm afraid I don't understand your last post. =(


The first "false" does NOT equal 0 when evaluated by the SMALL function. The result of:

=SMALL({false;9;false;false;13;14;15},1)

would be 9


The 2nd smallest is 13, then 14, then 15.

After that, formula will result in an error if you try to get the 5th smallest.


Again, this formula needs to be entered as an array (Ctrl+Shift+Enter) so that XL will look at all the values in a set, not simply the first one.
 
You must log in or register to reply here.
Back
Top