Solved

IF (ISNA) question

Posted on 2013-12-05
13
161 Views
Last Modified: 2013-12-07
Hi Guys, how do you turn this IFERROR formula into an IF(ISNA)?

=IFERROR(INDEX(Journals!P$5:P$1000,MATCH(TRUE,INDEX(Journals!B$5:B$1000=B572,0),0)),0)
0
Comment
Question by:Justincut
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39698833
=IF(ISNA(MATCH(TRUE,INDEX(Journals!B$5:B$1000=B572,0),0)),0,INDEX(Journals!P$5:P$1000,MATCH(TRUE,INDEX(Journals!B$5:B$1000=B572,0),0)))
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698952
Do you mind to post your sheet here? I want to show you somethings!
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698988
I just found you have some questions, I think this is one question!  I guess you are an accountant (same with me)!

Andrew
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39699048
justincut,

I see you are building on/ using solutions provided in previous threads, can you please close those threads if they were met with your satisfaction.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39699066
I am also a bit confused on a previous thread where you provided an ISNA() type formula you asked how to shorten it.  I provided you an IFERROR() solution to do that.  Now you are asking for the reverse again.... I am kind of confused....
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39699119
I agree with NB_VC. Perhaps you could give a wider explanation of what you are trying to do. Sometimes it's a good thing to split your requirement in to small chunks but sometimes (including here, I think) it would be beneficial if you try to explain your ultimate aim - there may be better ways than the way you are attempting.

I pointed this out in another thread but this construction:

=INDEX(Journals!P$5:P$1000,MATCH(TRUE,INDEX(Journals!B$5:B$1000=B572,0),0))

is almost always unnecessary. If you are looking up B572 in column B and returning a result from column P then a basic INDEX/MATCH to do that would be like this

=INDEX(Journals!P$5:P$1000,MATCH(B572,Journals!B$5:B$1000,0))

and if you want you can make that a VLOOKUP like this:

=VLOOKUP(B572,Journals!B$5:P$1000,15,0)

Now you can use ISNA round either of those, or even IFERROR, e.g. ISNA in conjunction with VLOOKUP

=IF(ISNA(VLOOKUP(B572,Journals!B$5:P$1000,15,0)),"",VLOOKUP(B572,Journals!B$5:P$1000,15,0))

regards, barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39699120
me too
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39699133
OK, sorry Justin, I was a little slow - you're using that construction because of the 255 character limit, I forgot about your previous thread.

...but that in itself is a good reason for a little more context - if you explain (briefly) what you are doing and why then you won't get idiots like me suggesting you change the formula for a different one!

So why ISNA rather than IFERROR, are you doing this in excel 2003 or is there another reason?

regards, barry
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39699135
But, the isna() and iserror() really is some difference in the accounting point of view.
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39699148
In the accounting point of view, isna() is not found (not included any errors), iserror() is included isna() and errors.
0
 

Author Comment

by:Justincut
ID: 39699302
Hi Guys, the reason why I need an ISNA() instead of an ISERROR() is because I need to retrieve a "VALUE#" message when there is a Vlookup not working. The problem is the Vlookup Cell on one day has a Value with several trade IDs on it (eg. 5678910, 2467891, 5734789) and on the next day, due to  a system bug, the sequence of Trade IDs changes to (2467891, 5678910, 5734789) so although the contents of the cell is the same, its in a different sequence so the Vlookup does not work. If I use an Iserror, it hides the fact there is a problem whereas if I use ISNA, it shows VALUE# so I can see there is a problem. Has anyone got any idea how to make this cell of trade ids go in a numerical sequence so the Vlookup works. I am an accountant at a bank and I have to do a Rec comparing trades every day so I do a vlookup on the previous day to see if there is a break
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39699410
As an example, assume that cell with multiple ID's in it is cell A9.

First, we will need to add a User Defined Function, for concatenating multiple values.

So go to the VB editor (Alt+F11), then Insert Module and paste this UDF:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Open in new window


Then this formula will convert that string of ID's to a numerically ascending sequence in one step:

=aconcat(SMALL(TRIM(MID(SUBSTITUTE(A9,",",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A9)-LEN(SUBSTITUTE(A9,",",""))+1))-1)),100))+0,ROW(INDIRECT("1:"&LEN(A9)-LEN(SUBSTITUTE(A9,",",""))+1))),", ")

Open in new window


This will need to be confirmed with CTRL+SHIFT+ENTER.

The only issue is that it will need to be included at each function in your formula that will need to reference this to do the look up.

You can also place this formula in another cell on it's own, maybe adjacent to the original, and re-write the VLOOKUPs to reference that new cell with the sorted strings.

Will that help?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39699822
I like NB_VC's suggestion of using a user-defined function to concatenate the string--but the sorting feature should be added to the UDF. That way, you won't need the array formula.

The UDF below should be pasted on a regular module sheet (just like a macro). It returns the values in the input parameters separated by commas and arranged in ascending order. You can then use it in a worksheet formula like:
=ConcatInOrder(E2,J2,I2,M2,V2)
Function ConcatInOrder(ParamArray vData() As Variant) As String
Dim v As Variant, vSorted As Variant
Dim i As Long, j As Long, n As Long
Dim bSorted As Boolean
Dim s As String, sep As String
sep = ","      'Separator between items in list
ReDim vSorted(1 To UBound(vData))
For Each v In vData
    If v <> "" Then
        n = n + 1
        vSorted(n) = v
    End If
Next
If n > 1 Then
    For i = 1 To n
        bSorted = True
        For j = 2 To n
            If vSorted(j) < vSorted(j - 1) Then
                v = vSorted(j - 1)
                vSorted(j - 1) = vSorted(j)
                vSorted(j) = v
                bSorted = False
            End If
        Next
        If bSorted = True Then Exit For
    Next
End If
For i = 1 To n
    s = s & sep & vSorted(i)
Next
ConcatInOrder = Mid$(s, Len(sep) + 1)
End Function

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro Capitalize 4 53
Struggling to Lock and grey out cells using VBA. Help please! 8 23
Consolidate xl 2010 worksheets with text 2 21
Copy value from a certain cell 5 24
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now