Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: very difficult CSE (array formula) question

  1. #1
    Guest

    Default

    I have two files. file1 has all of my data (like a database) and file2 extracts and displays significant data from file1.

    In the second file(file2) I am trying to write a CSE(Array Formula) which will print values into column A.

    It should:
    Look at all the values in file1!B2:B200
    If the value in a row of the file1!B column is "high" then it should print the exact text of the corresponding row of the file1!E column into the next available slot in the A column of file2.

    Example:
    file1 contains the following data:






    .ABCDE
    1...low......apples
    1...high......oranges
    1...low......pears
    1...high......grapes


    so . . . file2 should contain the following:





    .ABCDE
    1oranges............
    1grapes............



    Thanks.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I suppose you could use a variation of an Index Match (slightly different formula per column).

    e.g: {=INDEX([file1]$A$2:$E$200,MATCH(A2&"high",[file1]$E$2:$E$200&[file1]$A$2:$A$200,0),3)}

    ..would be used to pull File1Column C data into file 2.

    You may want to just suck it up and toss in a colunn to the left of you range in file A and write a quick concatenation formula (like =F2&B2 -assuming you just inserted a column to the left of the range) to get a combined criteria for a vlookup.

    That would look like (non-CSE): =Vlookup(A2&"high",[File1]$A$2:$F$200,4,false) ->to pull File1Colunn D data for example.

    Hope that helps
    Adam S.

Some videos you may like

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
  •