View Single Post
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default Find value in array



"Epinn" wrote in message
...

Bob, it is a cinch for you to point out the header problem. Kevin uses
A1:A3 in the original formula. If there is a header on row 1, we can't
just shift A1:A3 to A2:A4. We have to subtract MIN(ROW( and add on
1 etc. Am I understanding this correctly?



No, not really. All we need to do is subtract 1 to account for the one
header row. BUT ... should there be 2 header rows, you subtract 2, etc. I
just use

-MIN(A2:A4)+1

as if the OP is adjusting later, he will see the same range repeated, and
adjust that as well. Makes the formula more generic, without a hard-coded
value.


This reminds me of Biff's
formula (see below) the other day. So, I take a look again. Viola! It

is
similar. There is a header row and there is INDEX, therefore manipulation
of row numbers. I just realize that MIN is used to make it more dynamic
and we can hardcode ROW(A$2)+1, right?



Again, not quite. I just feel that by repeating the whole range it makes it
more obvious later when changing. Sometimes when something like ROW(A2) is
used that may be because we want to use the row as an index starting at two,
and therefore may not need changing if the range is changed. ROW(A2:A4)
returns an array, so MIN is used to extract the first value.


See, I told you all I don't want to
compartmentalize when I learn. I guess INDEX and header row issue can
really "stay" in my system now. By the way, I have yet to figure out the

role of
SMALL ( ) in the following formula. I have a post (last one) for the

thread by
Jared on Sept. 21 "how to make a(n) function to list all the same

records."
Hopefully I can resolve it later.


=IF(ROWS($1:1)<=COUNTIF($A$2:$A$10,$A$15),INDEX(B$ 2:B$10,
SMALL(IF($A$2:$A$10=$A$15,ROW(A$2:A$10)-ROW(A$2)+1),ROWS($1:1))),"")



Biff is building an array of row numbers that meet the criteria. SMALL is
used to get the first matching row number, then the second, then the third,
etc. (Not the ROWS($1:1))


Back to this thread. I am wondering if this kind of array formula INDEX,
MAX, ROW can be a drag on the system if we have a large table. I know
VLOOKUP can be hard on the system too. Please correct me if I am wrong.



Any formula can be a drag on the system if used a lot, but array formulae
are particulalrly hard.


VLOOKUP will stop searching when it has found the first exact match and
uses the first value obtained when there are duplicates. In the contrary,
this INDEX/MAX/ROW array formula will go through the entire table

regardless;
and as Roger explains, it will pick up the last value if more than one

match.
Also, it will do a logical test each time before it can even perform the

INDEX
portion of the formula. So, I have a concern on performance/speed.
Do I worry too much?



No, it is a justifiable concern. But in this casek, it is working on a very
samll array, so it shouldn't matter.

And there are always ways to speed a spreadsheet up.


Bob, I told you I could make evaluate formula crash. I used Biff's

formula
above to make it crash and he did warn us. I have to let you know that
the previous crash was nothing compared to the crash caused by the
formula on this thread. I wonder if the (evaluate formula) crash is any
indication of the formula's impact on the resource when we press F9.



I wouldn't have thought so.