Re: 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" <KevinVaughn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:D1336E06-85AC-4424-B91D-74767A958606@xxxxxxxxxxxxxxxx
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!

.

Relevant Pages

  • Re: Find value in array
    ... With Pen entered in cell A6 ... If Pen existed in cell D3 as well, then the third row of the array would ... writing device pencil pen marker sharpie ... The way I want it to work, if a user types in 'pen', it returns the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Find value in array
    ... In this case, when used within an array formula, Row ... multiply each of the 4 sets of Boolean values to produce the result. ... didn't have comma and ROW didn't have columns. ... The way I want it to work, if a user types in 'pen', it returns the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Find value in array
    ... I enjoyed your explanation and I especially like the part on duplicates. ... With Pen entered in cell A6 ... If Pen existed in cell D3 as well, then the third row of the array would ... The way I want it to work, if a user types in 'pen', it returns the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: combobox
    ... range when user types a non-matching value then you could do something like ... Private Sub ComboBox1_AfterUpdate ... > the array, is there a way to check the array and if it ...
    (microsoft.public.excel.programming)