Match function Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the "match" function and am having trouble with th
syntax of the "look up array" argument. I can get it to work for a singl
cell or a range of cells, but I need to look at 4 cells that are no
consecutive. I thought I could use the syntax{1,2,3,4} but I keep gettin
errors. Does anyone know if this is possible?
 
But there's nothing to stop you creating a contiguous range elsewhere linking
each of the cells to one of your 4 others, and then use the MATCH function on
that range.
 
One way working with numerical values


=MATCH(5,LARGE((A1,C1,E1),ROW(INDIRECT("1:"&COUNT(A1,C1,E1)))),0)

will lookup 5 in A1, C1 and E1 and if C1 holds 5 it will return 2
 
One way working with numerical values

=MATCH(5,LARGE((A1,C1,E1),ROW(INDIRECT("1:"&COUNT(A1,C1,E1)))),0)

will lookup 5 in A1, C1 and E1 and if C1 holds 5 it will return 2
Click to expand...

Which gives the position of 5 in the *sorted* list of values. Depending on what
the OP wants to do with this, this either provides no information (the position
in the original, possibly unordered list is needed, not the position in a sorted
list), or does too much to give the information needed (the OP only wants to
check that there's an instance of 5 in an arbitrary range). In the latter case,
if the arbitrary range is assumed to contain only numbers, that could be done
using

=INDEX(FREQUENCY(ArbitraryRange,C1+TINY*{-1;0;1}),2)>0

where TINY is a defined name referring to a 'small' positive number like 1E-12.

Finding the position of a value in an arbitrary range could be done using
hardcoded OFFSET calls that reproduce the arbitrary range's values as an array,
e.g.,

=MATCH(5,N(OFFSET(A1,0,{0,2,4})),0)


Digression:
-----------
FWLIW, while testing this, I came across unexpected behavior from
CELL("Address",.) when it's fed multiple area range. For example,

=CELL("Address",(A1:A3,A5:A7,A9:A11))

returns

$A$1,$A$5:$A$7,$A$9:$A$11

so it returns the top-left cell from the first area, but it returns the full
range addresses for all other areas. Further, multiple area ranges apparently
ignore parentheses (at least in XL8 - anyone want to test in later versions),
i.e.,

=CELL("Address",(IV65536,(A1:A3,A5:A7,A9:A11)))

returns

$IV$65536,$A$1:$A$3,$A$5:$A$7,$A$9:$A$11

So given an arbitrary range reference ArbitraryRange, Excel can return a
complete textref using the formula

=MID(CELL("Address",(IV65536,ArbitraryRange)),11,1024)

With defined name Seq referring to =ROW(INDIRECT("1:1024")), an array of
textrefs for each area in ArbitraryRange is given by

=MID(CELL("Address",(IV65536,ArbitraryRange)),
SMALL(IF(MID(","&CELL("Address",(IV65536,ArbitraryRange)),Seq,1)=",",Seq),
ROW(INDIRECT("2:"&AREAS(ArbitraryRange)))),
SMALL(IF(MID(CELL("Address",(IV65536,ArbitraryRange))&",",Seq,1)=",",Seq),
ROW(INDIRECT("2:"&AREAS(ArbitraryRange))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,ArbitraryRange)),Seq,1)=",",Seq),
ROW(INDIRECT("2:"&AREAS(ArbitraryRange)))))

Since this eats 6 levels of nested function calls, I couldn't make use of it for
this, but it seems interesting.
 
Harlan Grove said:
...

Which gives the position of 5 in the *sorted* list of values. Depending on what
the OP wants to do with this, this either provides no information (the position
in the original, possibly unordered list is needed, not the position in a sorted
list), or does too much to give the information needed (the OP only wants to
check that there's an instance of 5 in an arbitrary range).
Click to expand...

I agree with that, I didn't find any use for it either..
In the latter case,
if the arbitrary range is assumed to contain only numbers, that could be done
using

=INDEX(FREQUENCY(ArbitraryRange,C1+TINY*{-1;0;1}),2)>0

where TINY is a defined name referring to a 'small' positive number like 1E-12.

Finding the position of a value in an arbitrary range could be done using
hardcoded OFFSET calls that reproduce the arbitrary range's values as an array,
e.g.,

=MATCH(5,N(OFFSET(A1,0,{0,2,4})),0)


Digression:
-----------
FWLIW, while testing this, I came across unexpected behavior from
CELL("Address",.) when it's fed multiple area range. For example,

=CELL("Address",(A1:A3,A5:A7,A9:A11))

returns

$A$1,$A$5:$A$7,$A$9:$A$11

so it returns the top-left cell from the first area, but it returns the full
range addresses for all other areas. Further, multiple area ranges apparently
ignore parentheses (at least in XL8 - anyone want to test in later versions),
i.e.,

=CELL("Address",(IV65536,(A1:A3,A5:A7,A9:A11)))

returns

$IV$65536,$A$1:$A$3,$A$5:$A$7,$A$9:$A$11
Click to expand...

Same in XL10
So given an arbitrary range reference ArbitraryRange, Excel can return a
complete textref using the formula

=MID(CELL("Address",(IV65536,ArbitraryRange)),11,1024)

With defined name Seq referring to =ROW(INDIRECT("1:1024")), an array of
textrefs for each area in ArbitraryRange is given by

=MID(CELL("Address",(IV65536,ArbitraryRange)),
SMALL(IF(MID(","&CELL("Address",(IV65536,ArbitraryRange)),Seq,1)=",",Seq),
ROW(INDIRECT("2:"&AREAS(ArbitraryRange)))),
SMALL(IF(MID(CELL("Address",(IV65536,ArbitraryRange))&",",Seq,1)=",",Seq),
ROW(INDIRECT("2:"&AREAS(ArbitraryRange))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,ArbitraryRange)),Seq,1)=",",Seq) ,
ROW(INDIRECT("2:"&AREAS(ArbitraryRange)))))

Since this eats 6 levels of nested function calls, I couldn't make use of it for
this, but it seems interesting.
Click to expand...

I agree..

Regards,

Peo Sjoblom
 
Thank you. That would be a simple solution

----- Ken Wright wrote: ----

But there's nothing to stop you creating a contiguous range elsewhere linkin
each of the cells to one of your 4 others, and then use the MATCH function o
that range
 
Back
Top