Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Find value in array

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

Hi,

I don't know how many items you need to incorporate. Is it true that you must set up the table the way you mentioned?

If there is no rule, I will set up the table like this:-

Column 1 = the item
Column 2 = the category

pencil writing device
pen writing device
scrap paper paper
index card paper
paper clips fastener
tape fastener

etc. etc.

Then I use the VLOOKUP function (see Excel Help) to find an exact match. Pencil, pen, scrap paper etc. must be the leftmost column. Sort the above table in an ascending order to optimize speed.

This is just a thought and I use the KIS method. The experts may have other ideas. Please wait.

Epinn

"Brook6" wrote in message ...
I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 111
Default Find value in array

This array entered formula (entered using ctrl-shift-enter rather than just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

Kevin,

This looks great. But I don't understand the formula. Do you still have the link to Bob's paper as I want to learn too? When I see "*" I think of multiplication and coercing. Don't know what "*" represents here?

Wonder how we adjust the formula to take care of A6 being blank or having an entry (e.g. business card) not found in the array.

Appreciate guidance.

Epinn

"Kevin Vaughn" wrote in message ...
This array entered formula (entered using ctrl-shift-enter rather than just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Find value in array

This is very close to working, but when I enter a value from row1, it is
giving me the category from row2...and so on. When I enter a value from
row3, I get #REF error, since there are no values on row4.

Any additional thoughts?

Thanks!

"Kevin Vaughn" wrote:

This array entered formula (entered using ctrl-shift-enter rather than just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

Brook6,

Hmmm ......

I use the same data that you have provided in your post and the formula works fine for me *provided* the item I key into A6 *exists* in the table. I only get wrong results when I key in an item that does not exist in the table.

I am sure Kevin can help. By the way, I hope you don't mind me showing up.

Epinn

"Brook6" wrote in message ...
This is very close to working, but when I enter a value from row1, it is
giving me the category from row2...and so on. When I enter a value from
row3, I get #REF error, since there are no values on row4.

Any additional thoughts?

Thanks!

"Kevin Vaughn" wrote:

This array entered formula (entered using ctrl-shift-enter rather than just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Find value in array

Hi

It is multiplication.
With Pen entered in cell A6
A6=B1:E3 will return an array of
False, True, False, False
False, False, False, False
False, False, False, False

When this is multiplied by the 3 row numbers, 1, 2 and 3
The first row becomes 0,1,0,0 and all the others are all 0.
The MAX of these 12 values is therefore 1
INDEX(A1:A3,1) = A1 = Writing Device

If Pen existed in cell D3 as well, then the third row of the array would
become
0,0,3,0
And the Max would be 3, hence the result will be A3 = Fastener

So, if there are duplicates, it will return the highest row number (and
Heading) that has the value.
If there are no duplicates it will return the row and Heading that
contains the value.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Kevin,

This looks great. But I don't understand the formula. Do you still
have the link to Bob's paper as I want to learn too? When I see "*" I
think of multiplication and coercing. Don't know what "*" represents
here?

Wonder how we adjust the formula to take care of A6 being blank or
having an entry (e.g. business card) not found in the array.

Appreciate guidance.

Epinn

"Kevin Vaughn" wrote in message
...
This array entered formula (entered using ctrl-shift-enter rather than
just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do
it...
I want to have a table of text where the first column is a category
and the
adjacent cells in each row are the values for that category that users
might
want to find. The user types in a value, the function checks which
category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the
value
'writing device'

Any help appreciated. I have searched here and see some examples with
INDEX
and MATCH, but none seem to be doing this.

Thanks!



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Find value in array

Probably because you have a header row, and just adjusting to Row(2:4), etc.
indexes the wrong row.

Try

=IF(MAX((ROW(2:4)-MIN(ROW(2:4))+1)*(A7=B2:F4))=0,"",INDEX(A2:A4,MAX( (ROW(2:4
)-MIN(ROW(2:4))+1)*(A7=B2:F4))))

--
HTH

Bob Phillips

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

"Brook6" wrote in message
...
This is very close to working, but when I enter a value from row1, it is
giving me the category from row2...and so on. When I enter a value from
row3, I get #REF error, since there are no values on row4.

Any additional thoughts?

Thanks!

"Kevin Vaughn" wrote:

This array entered formula (entered using ctrl-shift-enter rather than

just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do

it...
I want to have a table of text where the first column is a category

and the
adjacent cells in each row are the values for that category that users

might
want to find. The user types in a value, the function checks which

category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the

value
'writing device'

Any help appreciated. I have searched here and see some examples with

INDEX
and MATCH, but none seem to be doing this.

Thanks!



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 111
Default Find value in array

Yes, I do have the link to that paper and will paste it below. I noticed
after I posted it that I forgot to use absolute addressing where I would
normally. I also didn't do any error checking but looks like Bob Phillips
provided a formula with error checking while Roger explained the mechanics of
the formula I offered.

http://www.emailoffice.com/excel/arrays-bobumlas.html

--
Kevin Vaughn


"Epinn" wrote:

Kevin,

This looks great. But I don't understand the formula. Do you still have the link to Bob's paper as I want to learn too? When I see "*" I think of multiplication and coercing. Don't know what "*" represents here?

Wonder how we adjust the formula to take care of A6 being blank or having an entry (e.g. business card) not found in the array.

Appreciate guidance.

Epinn

"Kevin Vaughn" wrote in message ...
This array entered formula (entered using ctrl-shift-enter rather than just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do it...
I want to have a table of text where the first column is a category and the
adjacent cells in each row are the values for that category that users might
want to find. The user types in a value, the function checks which category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the value
'writing device'

Any help appreciated. I have searched here and see some examples with INDEX
and MATCH, but none seem to be doing this.

Thanks!



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Find value in array

Thanks Bob and Kevin, this did the trick!

"Bob Phillips" wrote:

Probably because you have a header row, and just adjusting to Row(2:4), etc.
indexes the wrong row.

Try

=IF(MAX((ROW(2:4)-MIN(ROW(2:4))+1)*(A7=B2:F4))=0,"",INDEX(A2:A4,MAX( (ROW(2:4
)-MIN(ROW(2:4))+1)*(A7=B2:F4))))

--
HTH

Bob Phillips

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

"Brook6" wrote in message
...
This is very close to working, but when I enter a value from row1, it is
giving me the category from row2...and so on. When I enter a value from
row3, I get #REF error, since there are no values on row4.

Any additional thoughts?

Thanks!

"Kevin Vaughn" wrote:

This array entered formula (entered using ctrl-shift-enter rather than

just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do

it...
I want to have a table of text where the first column is a category

and the
adjacent cells in each row are the values for that category that users

might
want to find. The user types in a value, the function checks which

category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the

value
'writing device'

Any help appreciated. I have searched here and see some examples with

INDEX
and MATCH, but none seem to be doing this.

Thanks!






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

Roger,

I just want to let you know that your support means a lot to me. I read the Boolean writeup soon after you posted it. Most of it sank in. I was going to reread it again the next day but unfortunately I didn't do it before I read Kevin's formula. The "*" part from the Boolean post didn't quite sink in and I was thrown off by the fact that MAX ( ) didn't have comma and ROW ( ) didn't have columns. So, I asked about "*" again. Please don't be disappointed with me. Give me time. It is still hard for me to pick up Boolean operators when I don't see SUMPRODUCT ( ). You know I have been thinking about Pavlov and the dogs. Conditioned is the word. I must take * + -- beyond SUMPRODUCT. Please put up with me in the meantime. If I slip again, just shout "Boolean" and I shall remember.

I enjoyed your explanation and I especially like the part on duplicates.

Thanks so much.

http://en.wikipedia.org/wiki/Ivan_Pavlov

Epinn

"Roger Govier" wrote in message ...
Hi

It is multiplication.
With Pen entered in cell A6
A6=B1:E3 will return an array of
False, True, False, False
False, False, False, False
False, False, False, False

When this is multiplied by the 3 row numbers, 1, 2 and 3
The first row becomes 0,1,0,0 and all the others are all 0.
The MAX of these 12 values is therefore 1
INDEX(A1:A3,1) = A1 = Writing Device

If Pen existed in cell D3 as well, then the third row of the array would
become
0,0,3,0
And the Max would be 3, hence the result will be A3 = Fastener

So, if there are duplicates, it will return the highest row number (and
Heading) that has the value.
If there are no duplicates it will return the row and Heading that
contains the value.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Kevin,

This looks great. But I don't understand the formula. Do you still
have the link to Bob's paper as I want to learn too? When I see "*" I
think of multiplication and coercing. Don't know what "*" represents
here?

Wonder how we adjust the formula to take care of A6 being blank or
having an entry (e.g. business card) not found in the array.

Appreciate guidance.

Epinn

"Kevin Vaughn" wrote in message
...
This array entered formula (entered using ctrl-shift-enter rather than
just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do
it...
I want to have a table of text where the first column is a category
and the
adjacent cells in each row are the values for that category that users
might
want to find. The user types in a value, the function checks which
category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the
value
'writing device'

Any help appreciated. I have searched here and see some examples with
INDEX
and MATCH, but none seem to be doing this.

Thanks!




  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Find value in array

Hi

No problem.
MAX ( ) didn't have comma

No reason why it should. Max(A:A), Max(1:1), Max(mydata) where mydata is
a named range would all be very common uses of the Max() function. The
comma would only come into play where you are giving Max() a list of
separate items to compare e.g Max(A1:A4,16, Day(Today()), Counter) where
each argument can be an individual value or a range of values.

ROW ( ) didn't have columns ????

Row() returns the row number of the current row, when used on its own.
Row(1:1) is often used as a way of incrementing a number in a formula,
as it returns a value of 1 when first used, but as it is copied down a
column, it will become Row(2:2) etc and rerun 2, 3 and so on within the
formula. In this case, when used within an array formula, Row(1:3)
returns a vertical array 1,2,3 which, as I said previously, is used to
multiply each of the 4 sets of Boolean values to produce the result.

Column() does exactly the same thing in terms of returning a column
number and again Column (A:A) would step up as one copied across a row.
Column(A:D) used within an array formula, would return a horizontal
array of 1,2,3,4 and would have been used if the labels being Indexed
in the table were in the first row, and we were trying to determine
which column had produced our result.
Try it out, insert a new row 1 above the data block from the example we
have been using and put Epinn, Roger, Fred and Harry in B1:E1. Then use
{=INDEX(B1:E1,MAX(COLUMN(A:D)*(A7=B2:E4)))}
and you would find that the Pen was not only a writing device, but
belonged to Roger!!!

I must take * + -- beyond SUMPRODUCT

Yes. Whilst Sumproduct is a useful function, it is but one of many in
the whole range available in Excel and all of the arithmetic operators
are used both on their own and within all of the functions. If you
forget Sumproduct for a while, the tress may become a little
clearer<vbg


--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I just want to let you know that your support means a lot to me. I read
the Boolean writeup soon after you posted it. Most of it sank in. I
was going to reread it again the next day but unfortunately I didn't do
it before I read Kevin's formula. The "*" part from the Boolean post
didn't quite sink in and I was thrown off by the fact that MAX ( )
didn't have comma and ROW ( ) didn't have columns. So, I asked about
"*" again. Please don't be disappointed with me. Give me time. It is
still hard for me to pick up Boolean operators when I don't see
SUMPRODUCT ( ). You know I have been thinking about Pavlov and the
dogs. Conditioned is the word. I must take * + -- beyond SUMPRODUCT.
Please put up with me in the meantime. If I slip again, just shout
"Boolean" and I shall remember.

I enjoyed your explanation and I especially like the part on duplicates.

Thanks so much.

http://en.wikipedia.org/wiki/Ivan_Pavlov

Epinn

"Roger Govier" wrote in message
...
Hi

It is multiplication.
With Pen entered in cell A6
A6=B1:E3 will return an array of
False, True, False, False
False, False, False, False
False, False, False, False

When this is multiplied by the 3 row numbers, 1, 2 and 3
The first row becomes 0,1,0,0 and all the others are all 0.
The MAX of these 12 values is therefore 1
INDEX(A1:A3,1) = A1 = Writing Device

If Pen existed in cell D3 as well, then the third row of the array would
become
0,0,3,0
And the Max would be 3, hence the result will be A3 = Fastener

So, if there are duplicates, it will return the highest row number (and
Heading) that has the value.
If there are no duplicates it will return the row and Heading that
contains the value.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Kevin,

This looks great. But I don't understand the formula. Do you still
have the link to Bob's paper as I want to learn too? When I see "*" I
think of multiplication and coercing. Don't know what "*" represents
here?

Wonder how we adjust the formula to take care of A6 being blank or
having an entry (e.g. business card) not found in the array.

Appreciate guidance.

Epinn

"Kevin Vaughn" wrote in message
...
This array entered formula (entered using ctrl-shift-enter rather than
just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do
it...
I want to have a table of text where the first column is a category
and the
adjacent cells in each row are the values for that category that users
might
want to find. The user types in a value, the function checks which
category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the
value
'writing device'

Any help appreciated. I have searched here and see some examples with
INDEX
and MATCH, but none seem to be doing this.

Thanks!





  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

Thank you all. Kevin, I appreciate the link. I can read all I can without knowing how to apply what I have learned. Hopefully, I can improve with practice. Kevin didn't just know how to apply MAX(ROW( )) but he massaged it. Good role model. I thank Roger for his most recent post. My comments on MAX and ROW should illustrate my point about me learning how to run before ...... MAX and ROW are simpler functions. ;) Okay, I am making excuses. For MAX, I was looking for a comma in the place of "*" although I was aware that MAX(A:A) was perfectly all right. For ROW I was used to see ROW(C10) i.e. column and row. I will try to find Roger's pen later. Thanks for the bonus.

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? 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? 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))),"")

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

Last but not least, I want to thank Brook6 for the question as I have learned so much with one thread. I also hope that he/she understands that I was just thinking aloud in my very first post on this thread. I should learn to suppress that urge too on top of giving SUMPRODUCT a break. If I can't suppress that urge, my teachers will come and save the day, right? ;)

Thank you for everything. Have a good weekend!

Epinn

"Bob Phillips" wrote in message ...
Probably because you have a header row, and just adjusting to Row(2:4), etc.
indexes the wrong row.

Try

=IF(MAX((ROW(2:4)-MIN(ROW(2:4))+1)*(A7=B2:F4))=0,"",INDEX(A2:A4,MAX( (ROW(2:4
)-MIN(ROW(2:4))+1)*(A7=B2:F4))))

--
HTH

Bob Phillips

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

"Brook6" wrote in message
...
This is very close to working, but when I enter a value from row1, it is
giving me the category from row2...and so on. When I enter a value from
row3, I get #REF error, since there are no values on row4.

Any additional thoughts?

Thanks!

"Kevin Vaughn" wrote:

This array entered formula (entered using ctrl-shift-enter rather than

just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.
--
Kevin Vaughn


"Brook6" wrote:

I am trying to do something that seems simple, but not sure how to do

it...
I want to have a table of text where the first column is a category

and the
adjacent cells in each row are the values for that category that users

might
want to find. The user types in a value, the function checks which

category
it is in and returns that value.

Here is sample data; first value in row is category, other values are
possible user entries
writing device pencil pen marker sharpie
paper scrap paper notepad post it index card
fastener tacks staples paper clips tape

The way I want it to work, if a user types in 'pen', it returns the

value
'writing device'

Any help appreciated. I have searched here and see some examples with

INDEX
and MATCH, but none seem to be doing this.

Thanks!




  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

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.





  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Find value in array

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.




  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Find value in array

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.






  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Find value in array

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.








  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Find value in array

Good point!

--
Regards

Roger Govier


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










  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

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.











  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

The data is in B2:F4, so Biff used that throughout.

Please remember that I substitute B2:F4 into Biff's original formula to suit Brook6's table. Hope there is no copyright. ;) I have included Biff's original formula in my other post for your reference. I am sure Bob's analysis still stands.

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.









  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Find value in array

<< 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 think it is MATCH ( ).

Epinn

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










  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Find value in array

Hi

Raising any number to the power of 0 results in a value of 1, so the
result of COLUMN(B:F) would be 2,3,4,5,6 which when raised to the power
of zero results in 1,1,1,1,1

The easiest way to consider it is by starting with a number like 2^4
which gives 16, and 2^3 which gives 8 and 2^2 which gives 4, So as we
come down by each power, we are dividing by the original number so
extending onward, 2^1 gives 2 and 2^0 gives 1. The only exceptions to
this of course are 0 and 1 themselves which when raised to any power
will remain as 0 and 1.

If you continue the series, 2^-1 would give 0.5 and 2^-2 would give 0.25
in other words, each is the reciprocal of the positive power.

--
Regards

Roger Govier


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










  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Find value in array

I think we took that as read Epinn; I am sure that there is no copyright; I
am sure that my analysis still holds good, after all as you yourself said,
you adapted it without understanding it.

--
HTH

Bob Phillips

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

"Epinn" wrote in message
...
The data is in B2:F4, so Biff used that throughout.


Please remember that I substitute B2:F4 into Biff's original formula to suit
Brook6's table. Hope there is no copyright. ;) I have included Biff's
original formula in my other post for your reference. I am sure Bob's
analysis still stands.

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.










  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Find value in array

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.












  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Find value in array


I know this thread is long closed, but, I THOUGHT that 0^0 would still be 1
(because I thought every value raised to the 0th power was 1,) but when I
tried it in Excel, I got #num (FWIW.)

--
Kevin Vaughn


"Roger Govier" wrote in message
...
Hi

Raising any number to the power of 0 results in a value of 1, so the
result of COLUMN(B:F) would be 2,3,4,5,6 which when raised to the power
of zero results in 1,1,1,1,1

The easiest way to consider it is by starting with a number like 2^4
which gives 16, and 2^3 which gives 8 and 2^2 which gives 4, So as we
come down by each power, we are dividing by the original number so
extending onward, 2^1 gives 2 and 2^0 gives 1. The only exceptions to
this of course are 0 and 1 themselves which when raised to any power
will remain as 0 and 1.

If you continue the series, 2^-1 would give 0.5 and 2^-2 would give 0.25
in other words, each is the reciprocal of the positive power.

--
Regards

Roger Govier


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














  #27   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Find value in array

"Kevin Vaughn" wrote...
I know this thread is long closed, but, I THOUGHT that 0^0 would still be 1
(because I thought every value raised to the 0th power was 1,) but when I
tried it in Excel, I got #num (FWIW.)

....

#NUM! is the appropriate result, just as it's the same result as given
by LOG(0). 0^0 is undefined for the very simple reason that if 0^0 =
1, then 0^0 = 0^(n - n) for any n, and so 0^(n - n) = 0^n / 0^n = 0 /
0. But *any* number divided by zero is undefined, *including* zero
itself. All sorts of inconsistencies arise if 0/0 = 0^0 = 1, which is
why it doesn't.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display an array of references andy62 Excel Worksheet Functions 1 July 6th 06 03:36 AM
how to find all matches in an array Greg Excel Discussion (Misc queries) 2 August 31st 05 09:04 PM
How do I find a value in an array (VLOOKUP? HLOOKUP?) M Skabialka New Users to Excel 2 March 11th 05 03:52 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 09:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"