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!