Biff was referring to the fact that there was a simpler formula (which you
should know, you joined in that thread). Looking at it, and adjusting to our
OPs data and his starting row, we can use

=INDEX(A2:A4,MIN(IF(B2:F4=A7,ROW(A2:A4)-MIN(ROW(A2:A4))+1,"")))

as an array formula.

Time to put this one to bed?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
All this ^0 business is beyond me. I might even have a problem deciphering
INDEX/MAX/ROW formula. However, I can read and compare.

Under the other thread, the poster had the same scenario as Brook6 had.

While Biff suggested INDEX/MATCH, interestingly enough another expert
suggested INDEX/MIN/ROW.
************************************************** **************************
******
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D2:D6,MATCH(TRUE,MMULT(--(A2:C6=G2),TRANSPOSE(COLUMN(A2:C6)^0))0,0))

=IF(SUM(--(A1:C5=B8)),INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5) ,""))),"N/A")
also array entered

************************************************** **************************
******
How does Biff think of his formula compared to INDEX/MIN/ROW?

In Biff's words: "I sure went with overkill on that one!"

Bob: "Good coding practice IMO."

Biff's formula works perfectly as it takes care of the entries that are not
found in the table. I think it gives an NA error.

However, for the other formula, be it under this thread or the other thread,
I had to draw the creators' attention to the fact that the formulae did not
take care of errors.

So, I am wondering if there is some kind of built-in error checking in
Biff's formula. Which function does the job automatically?

I probably won't understand, but as always I feel like chatting.

Thank you for reading.

Epinn

"Bob Phillips" wrote in message
...
Because he is addressing the data presented, rather than throwing up some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used that
throughout. If the OP then wanted to extend it for a bigger data range, it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Bob

I also noticed Biff's clever use of ^0 to create the array of 1's, but
wondered why you would want to use
TRANSPOSE(COLUMN(B2:F2)) to get a vertical array of 1's rather than
using ROW(1:5)^0 ?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
There is what little gem in that formula IMO

COLUMN(B2:F2)^0

a great way to get an array of 1's. Must remember that one.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
I haven't digested everything in this thread yet but I can't wait to
tell
everyone about my discovery.

In Brook6's original post, he/she mentioned about wanting to use INDEX
and
MATCH. Brook6, I am late but here is the formula, an alternative to
MAX,
ROW etc. Please note that this is an array formula (CSE).


{=INDEX(A2:A4,MATCH(TRUE,MMULT(--(B2:F4=A7),TRANSPOSE(COLUMN(B2:F4)^0))0,0)
)}

I can't remember which smart person came up with the above formula.
Biff,
was that you? I just changed the cell reference and tested the
formula and
it gave the exact same results as the MAX/ROW formula if the item is
found.
If not found, MAX/ROW formula gives a blank whereas INDEX/MATCH gives
#N/A.
I don't know which formula is more efficient. Both are array
formulae. For
MAX/ROW we have to do a logical test each time whereas INDEX/MATCH has
to go
through MMULT, TRANSPOSE etc.

The truth is I don't know how to decipher this INDEX/MATCH formula as
MMULT
always drives me crazy. I must thank Kevin for telling me about
MAX/ROW. I
may not have fully analyzed it yet but at least it may be something
that I
can grasp. If I have a choice in setting up the database/array, I'll
probably just use the KIS method (i.e. one to one - writing device
pen,
writing device pencil, writing device marker etc.) and then use
VLOOKUP. I
have a feeling that this is not as hard on the resource if we have a
large
array. Bob, please correct me if I am wrong. (Ignore me, Brook6, I
am just
trying to learn, not criticizing.)

I feel like showing an alternative formula in this thread for all the
readers. Thank you for your attention.

Epinn

"Bob Phillips" wrote in message
...


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