First of all, #N/A is not an "error",
it is an ANSWER to the question of whether the value exists in the range!
So, if the answer to vlookup is #N/A, then iferror() will still be false,
because it's not an "error".
what you're looking for is =ISNA
Secondly, you're testing to see if a "blank" cell returns an error when looking
in the vlookup range.
Personally, I think you've gotten lucky since I don't know why it doesn't
recognize the blank cells after row 78 and return a match to them.
Wouldn't it be more accurate to check to see if the cell is blank?
=IF(A2<>"",
VLOOKUP(A2,DATA!$A$1:$C$300000,3,FALSE),
VLOOKUP(B2,DATA!$B$1:$C$300000,2,FALSE))
The next problem you have is with the "cleanliness" of your data.
You've evidently imported the data from some other source.
For some reason, Excel recognizes it as "text" rather than numeric.
But on the "Output" sheet, when you manually enter a value, Excel treats it as
a number.
Coincidentally, if you EDIT a value on the Data sheet, then simply hit a
"return", Excel converts the value to a number.
Which evidently was done, since some of the values ARE recognized as numeric.
Because I've encountered this frequently, I use a macro to convert the text to
numbers by replacing 0-9 with 0-9.
My macro looks like:
Sub Clean_Numbers()
Dim i As Integer
Dim rng As Range
ActiveSheet.UsedRange.Select
For i = 48 To 57
Selection.Replace _
What:=Chr(i), _
Replacement:=Chr(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub
hope this helps
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Ashu k <direct2as...@gmail.com>
>To: Mandeep Baluja <rockerna...@gmail.com>; excel-macros
><excel-macros@googlegroups.com>
>Sent: Tuesday, January 6, 2015 11:38 AM
>Subject: Fwd: $$Excel-Macros$$ Re: Help required for Match DATA
>
>
>
>Dear Mandeep Sir,
>
>
>Please help on this. The sheet is provided by you is providing NA#. Please
>Help.
>
>
>Thanks
>Ashu Kenith
>
>---------- Forwarded message ----------
>From: Ashu k <direct2as...@gmail.com>
>Date: Wed, Dec 31, 2014 at 11:52 AM
>Subject: Fwd: $$Excel-Macros$$ Re: Help required for Match DATA
>To: Mandeep Baluja <rockerna...@gmail.com>, excel-macros
><excel-macros@googlegroups.com>
>
>
>
>Dear Mandeep Sir,
>
>
>Please help me on the same issue. Thse sheet is provided by you is not giving
>correct output.
>
>
>It gives NA# again. Kindly run macro and see the result in Cell C1 and C2.
>Sheet is attached.
>
>
>Thanks
>Ashu Kenith
>
>
>
>---------- Forwarded message ----------
>From: Ashu k <direct2as...@gmail.com>
>Date: Tue, Dec 23, 2014 at 11:06 AM
>Subject: Re: $$Excel-Macros$$ Re: Help required for Match DATA
>To: excel-macros <excel-macros@googlegroups.com>
>
>
>
>Dear Mandeep Sir,
>
>
>It gives NA# again. Kindly run macro and See the result in Cell C1 and C12.
>Sheet attached.
>
>
>
>
>Thanks
>Ashu Kenith
>
>
>
>
>On Tue, Dec 23, 2014 at 10:52 AM, Mandeep Baluja <rockerna...@gmail.com> wrote:
>
>Now this macro works for 3 lakh rows you can increase the range as per your
>convenience.
>>
>>
>>Regards,
>>Mandeep Baluja
>>LearningZmyPassion
>>https://www.linkedin.com/profile/view?id=312532939
>>https://www.facebook.com/VBAEXCELSQL?ref=hl
>>
>>
>>
>>On Wednesday, December 17, 2014 6:30:29 PM UTC+5:30, Ashu k wrote:
>>Dear Seniors,
>>>
>>>
>>>I have data in sheet 1
>>>
>>>
>>>Name Class Roll NO. Exam Date
>>>
>>>
>>>
>>>
>>>and,
>>>
>>>
>>>I have data in Sheet 2
>>>
>>>
>>>Name Class Roll No. Exam Date
>>>
>>>
>>>
>>>
>>>But some Names are not in Name Column and Some Class are not in Class
>>>Column, I want to get all Roll No. in Sheet 2 from Sheet 1.
>>>
>>>
>>>Example data attached. Please help me.
>>>
>>>
>>>Regards
>>>Ashu Kenith
>>--
>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>https://www.facebook.com/discussexcel
>>
>>FORUM RULES
>>
>>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not
>>get quick attention or may not be answered.
>>2) Don't post a question in the thread of another member.
>>3) Don't post questions regarding breaking or bypassing any security measure.
>>4) Acknowledge the responses you receive, good or bad.
>>5) Jobs posting is not allowed.
>>6) Sharing copyrighted material and their links is not allowed.
>>
>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>members are not responsible for any loss.
>>---
>>You received this message because you are subscribed to the Google Groups "MS
>>EXCEL AND VBA MACROS" group.
>>To unsubscribe from this group and stop receiving emails from it, send an
>>email to excel-macros+unsubscr...@googlegroups.com.
>>To post to this group, send email to excel-macros@googlegroups.com.
>>Visit this group at http://groups.google.com/group/excel-macros.
>>For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
