This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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)

=IFERROR(INDEX(Journals!P$

Andrew

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.

I pointed this out in another thread but this construction:

=INDEX(Journals!P$5:P$1000

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

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

=VLOOKUP(B572,Journals!B$5

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

=IF(ISNA(VLOOKUP(B572,Jour

regards, barry

...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

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
```

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))),", ")
```

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?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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

Open in new window