Vlookup "Value Not Available" (#N/A) Error

  • Thread starter Thread starter Guest
  • Start date Start date
MsBeverlee said:
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?
Click to expand...

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

KL said:
This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


MsBeverlee said:
Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


:

... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all of your help. For whatever reason, it still is not working.
I'm not sure why, but I don't think it's the reasons you suggested. And I
only say that because I am copying and pasting Dave's suggested formula right
into my spreadsheet. But for whatever reason, it's only returning the
"PENDING" result regardless of whether it's a "Value Not Available" (#N/A)
result or not. By just using my original formula

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

this works perfectly and returns the correct results. I was only trying to
replace the #N/A with "PENDING" for those cells that data isn't available in
the TRAVEL MANIFEST spreadsheet. I thought I could use an IF component to
accomplish this, but apparently it's not possible. I don't know why the IF
component doesn't work. I guess I'll just have to deal with it.

I do appreciate all of your suggested help. Thanks so much!



KL said:
MsBeverlee said:
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?
Click to expand...

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

KL said:
This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


:

... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
Click to expand...
 
My formula didn't use A2:Q66 and offset by 3 columns.

But are you sure you used ctrl-shift-enter to enter the formula.
Thanks for all of your help. For whatever reason, it still is not working.
I'm not sure why, but I don't think it's the reasons you suggested. And I
only say that because I am copying and pasting Dave's suggested formula right
into my spreadsheet. But for whatever reason, it's only returning the
"PENDING" result regardless of whether it's a "Value Not Available" (#N/A)
result or not. By just using my original formula

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

this works perfectly and returns the correct results. I was only trying to
replace the #N/A with "PENDING" for those cells that data isn't available in
the TRAVEL MANIFEST spreadsheet. I thought I could use an IF component to
accomplish this, but apparently it's not possible. I don't know why the IF
component doesn't work. I guess I'll just have to deal with it.

I do appreciate all of your suggested help. Thanks so much!



KL said:
MsBeverlee said:
It still isn't working properly. For instance, it is giving me the result
"PENDING" even though the data is there. It should be returning the result
"NL2PWW" because it should be pulling the data from column 3 of my TRAVEL
MANIFEST spreadsheet. And if there is no data in column 3 of my TRAVEL
MANIFEST spreadsheet, then it should return the result "PENDING".
What am I doing wrong?
Click to expand...

As already mentioned by Dave, it's easy to screw up with such a long formula and I mentioned before that it can affect the
performance of the file as a whole. I would go with Dave's solution or even leave the initial formula as is and have the error
values hidden by Conditional Formats (at the end of the day it is about the sheet looking pretty :-))

I repeted the tests and formula as posted by me works well. There are several things that can wrong with the formula such as:
1) the values you are searching are not exactly the same in your table although they might look the same (some extra spaces or
ather invisible caracters)
2) when sent to a newsgroup troughth OE the lines in the message are broken so when you copy the formula back to your sheet some
characters may be lost or added by mistake (e.g. spaces in the name of the remote workbook)
3) if you are not using an English version of Office, the names of functions and separators must be translated to your locale

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

:

This worked for me (don't forget to use CTRL+SHIFT+ENTER when introducing the formula):

=IF(ISNA(MATCH(1,(B47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0)),"PENDING",INDEX('[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


Hi, KL -

I tried it but it didn't work, so I'm sure I'm doing something wrong. Here
is my formula:

=INDEX('[TRAVEL MANIFEST_Master.xls]Sheet1'!$A$2:$Q$66,MATCH(1,(B47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$A$2:$A$66)*(C47='[TRAVEL
MANIFEST_Master.xls]Sheet1'!$B$2:$B$66),0),3)

It is yielding the correct result. But now I want to add the IF componenet
so that if the result is #N/A (because some of the info is missing in the
other worksheet), I want it to return "PENDING" instead of the #N/A. How do
I do this?

Thanks so much!


:

... I want to know if this same
formula wan work adding an IF component? If the information cannot be found,
instead of returning the error #N/A, I want it to return "PENDING". Can this
be done?

It will work, but...(!) the recalculation may become visbly slower if the actual ranges are very long and there are many
instances
of this formula on the sheet.

=if(isna(match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)),"",index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0)))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
Click to expand...
Click to expand...
 
This code works well for me:

'-----Code Start-----
Option Explicit

'IMPORTANT!!!
'Make sure your original file "TRAVEL MANIFEST_Master.xls"
'is not in the same folder as the file from which you run this code.
Sub Test()
Dim oWB As Workbook
Dim strWBName As String
Dim arrRng As Variant
Dim strFormulaPart1 As String
Dim strFormulaPart2 As String

strWBName = ThisWorkbook.Path & "\" & "TRAVEL MANIFEST_Master.xls"

If Dir(strWBName) <> "" Then
On Error Resume Next
Set oWB = Workbooks(Dir(strWBName))
If Err.Number <> 0 Then Set oWB = Workbooks.Open(strWBName)
On Error GoTo 0
Else
Set oWB = Workbooks.Add
oWB.SaveAs strWBName
End If
With oWB.Worksheets(1)
.[A47:C47] = Array("John", "Smith", 123456)
arrRng = Array(.[A2:A66].Address(True, True, xlA1, True), _
.[B2:B66].Address(True, True, xlA1, True), _
.[C2:C66].Address(True, True, xlA1, True))
End With
strFormulaPart1 = "=IF(ISNA(MATCH(1,(B47=" & arrRng(0) & ")*(C47=" & arrRng(1) & "),0)),""PENDING"",DUMMY())"
strFormulaPart2 = "INDEX(" & arrRng(2) & ",MATCH(1,(B47=" & arrRng(0) & ")*(C47=" & arrRng(1) & "),0))"

With Worksheets(1)
.Range("B47:C47") = Array("John", "Smith")
.Range("B48:C48") = Array("James", "Smith")
With .Range("D47")
.FormulaArray = strFormulaPart1
.Replace "DUMMY()", strFormulaPart2
.AutoFill Destination:=.Resize(2)
End With
End With

oWB.Close True
Set oWB = Nothing
End Sub
'-----Code Finish-----

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
 
Back
Top