MATCH does not work on a UDF-generated array

[pinkfloydfan]

Hi there

Can anybody explain why I have this particular problem?

I have written a UDF that outputs a 2-dimensional array. If this UDF is
called F(x) why does the following give a #N/A error?

MATCH(X6,F(x))

If I can't find a way to make this work then I guess I'll have to write
a UDF of my own to solve the problem.

Cheers.

 

[Niek Otten]

A UDF cannot change anything in a workbook.

If that is not an answer to your question; what is the code of your UDF and what are the values of the cells involved?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi there
|
| Can anybody explain why I have this particular problem?
|
| I have written a UDF that outputs a 2-dimensional array. If this UDF is
| called F(x) why does the following give a #N/A error?
|
| MATCH(X6,F(x))
|
| If I can't find a way to make this work then I guess I'll have to write
| a UDF of my own to solve the problem.
|
| Cheers.
|

 

[pinkfloydfan]

Hi Niek

The UDF is not changing anything on the workbook. The code is too
complex but suffice to say that on its own it works fine...it outputs a
2-column set of results where the first column is a list of dates and
the second is a list of numbers associated with each date.

I don't want to have to perform the MATCH (or a VLOOKUP) on the output
as that would be unwieldy...it would be much neater if I could use the
UDF as an indirect reference to an array within MATCH.

Does that make sense?

Thanks

 

[Alan Beban]

Hi Niek

The UDF is not changing anything on the workbook. The code is too
complex but suffice to say that on its own it works fine...it outputs a
2-column set of results where the first column is a list of dates and
the second is a list of numbers associated with each date.

I don't want to have to perform the MATCH (or a VLOOKUP) on the output
as that would be unwieldy...it would be much neater if I could use the
UDF as an indirect reference to an array within MATCH.

Does that make sense?

Thanks

What is X6?

Alan Beban

 

[Peter T]

When you say your function returns a 2D array do you mean multi row in two
columns.

If so the Match function needs a single row or column.

If you want to match say the second column of the array returned by the UDF
try

=MATCH(X6,index(F(x),,2)

I assume X6 is a cell ref & x is a value being the UDF arg'

Regards,
Peter T

 

[pinkfloydfan]

Any cell reference you want...

 

[pinkfloydfan]

Perfect!

Thanks a lot Peter

 

[Peter T]

I omitted the closing bracket (in my previous post)

=MATCH(X6,index(F(x),,2)

=MATCH(X6,index(F(x),,2))

Glad you got it working

Regards,
Peter T