Has anyone used arrayfunctions from Alan Beban?

[SteveT]

The website is http://home.pacbell.net/beban/ and there is a function in
there that would really help me (arraymatch) and I can't figure out how to
use it properly. When I use the wizard and enter the value and the array
range it correctly shows the output data at the bottom of the window but I
don't know how to make it display in the worksheet. It is supposed to output
the results in a two column array indicating the row and column of all
matches. All I get is the first match row number.

Thanks
Steve

 

[Mike Middleton]

Steve -

I haven't used Alan Beban's functions, but since it seems to be an array
function, you should first select the range where you want the results to
appear (from your description it should be two columns and many rows, and
it's usually OK to select more rows than you really need), second type the
equal sign and the function with its arguments but don't press Enter,
finally hold down Control and Shift while you press Enter, thus "array
entering" the function.

- Mike
http://www.mikemiddleton.com

 

[Bernd]

Hi Steve,

An example:

Enter into cells A1:C4
a b c
b a c
A B C
C A B

Then select cells D1:E4 and enter as array formula (enter with CTRL +
SHIFT + ENTER, not only with ENTER!):
=ArrayMatch("a",$A$1:$C$4)

Your output will be
1 1
2 2
3 1
4 2

Array-enter into F1:G4
=ArrayMatch("B",$A$1:$C$4,FALSE,1,TRUE)
and your output will be
3 2
4 3
#NV #NV
#NV #NV

Regards,
Bernd

 

[Alan Beban]

Hi Steve,

An example:

Enter into cells A1:C4
a b c
b a c
A B C
C A B

Then select cells D1:E4 and enter as array formula (enter with CTRL +
SHIFT + ENTER, not only with ENTER!):
=ArrayMatch("a",$A$1:$C$4)

Your output will be
1 1
2 2
3 1
4 2

Because the default mode ignores case.

Array-enter into F1:G4
=ArrayMatch("B",$A$1:$C$4,FALSE,1,TRUE)
and your output will be
3 2
4 3
#NV #NV
#NV #NV

Because the 5th argument of TRUE requires case-matching.

You'd get the same result with
=ArrayMatch("B",$A$1:$C$4,,,TRUE)

Alan Beban