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!