Array formula to lookup for values and create a data Validat

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Array formula to lookup for values and create a data Validat

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    Quebec-Canada
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello:

    I am trying to use and array formula to extract certain values from a database and return a list of extracted value which will be use later for data Validation list (tools / Validation / list). Here is an example of what I tried so far:


    ******** ******************** ************************************************************************>
    Microsoft Excel - Classeur1.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    The Data:
    2
    Database
    3
    Field1
    (Scanned values)
    Field2 - option 1
    (value to be returned to the Validation list)
    Field2 - option 2
    (value to be returned to the Validation list)
    4
    Hello 1 013 S345s
    5
    Bye 204 10-10-34-409
    6
    Goodbye 4 056 a1D45
    7
    Hello 234 g56jJ
    8
    Good morning 5 600 d459s
    9
    Hello 4 555 1jsj8-js2
    10
    11
    Lookup value (range create only for array formula purpose
    12
    Hello
    13
    Hello
    14
    Hello
    15
    Hello
    16
    Hello
    17
    Hello
    18
    19
    Progress to date:
    20
    Range of arrays formula that creates the Validation list
    21
    FormulaValue returned by the formula
    22
    {=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;1)}4555
    23
    {=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;2)}1013
    24
    {=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;3)}234
    25
    {=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;4)}0
    26
    ..
    27
    {=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;n)}0
    28
    Feuil1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Problems:
    1 - The above template only works with numeric data to be returned (field2)
    2 - I need to copy the "Lookup value" in a range of the same size than the "Scanned values" range

    Needed:
    1 - I need to return text value to the data Validation list (as in Field2 - option 2).
    2 - I would prefer not to have to create a range for the Lookup value.
    The Lookup value need to be in one cell only in order to simplify the template.
    3 - How can I do that???

    Thank you !

    Louis

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Quebec-Canada
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Forget about the present message,

    Its my first try with Colo's add-in

    Louis

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com