Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: formula help

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formula help

    i have the attached file which explain my case, i need a formula that read a specif data from table2 & align it in table 1 for the corresponding name, highlighted in red.

    TIA

    dubdub
    Last edited by dubdub; 2012-08-02 at 09:23.
    TIA
    dubdub

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Put: =IF(I$2=$G3,$E3,"") in I3 and fill across and down.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Kweaver, but may be i did not describe the question enough what i am after is a formula that will match columns a&e in the first table with column h & the array i3:04 in the second table and if match exist it will pick up the right value from i2:02 and put it in column g3....

    dubdub
    Last edited by dubdub; 2012-08-01 at 21:04.
    TIA
    dubdub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm afraid I don't understand what you want done.

    Do you want the year (in red) to be generated based on the location of a "1" in table 2?
    Last edited by kweaver; 2012-08-01 at 21:25.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The red column represent what the formula outcome supposed to be based on the match if exist and it would read from table2
    TIA
    dubdub

  6. #6
    New Lounger
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try

    =INDEX($I$2:$O$2,MATCH($E3,$I3:$O3,0))

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    my appology, the attached sexample has to do the match based in two columns in table 1, column a&e with table 2 columnm h for the name and the array i3:04 for count and if match exist it will put the year value from range i2:02 in column g.
    Attached Files Attached Files
    Last edited by dubdub; 2012-08-02 at 09:28.
    TIA
    dubdub

  8. #8
    New Lounger
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is what my formula does.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Bob, but if you consider the example i attached lately, it works for the first name in table-1 because there is a match in table 2 but the next name it still give a value of 2014 although the names are not matched.the value supposed to be blank.
    TIA
    dubdub

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i still have hope....
    TIA
    dubdub

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    How's this in G3, then fill down:

    =IF(ISERROR(MATCH(D3,$H$3:$H$4,0)),"",SUMPRODUCT(( $I$2:$O$2)*INDIRECT("I"&(2+MATCH(D3,$H$3:$H$4,0))& ":O"&(2+MATCH(D3,$H$3:$H$4,0)))))

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If the "count" could be other than a "1", change to this ARRAY formula:

    =IF(ISERROR(MATCH(D3,$H$3:$H$7,0)),"",SUMPRODUCT(( $I$2:$O$2)*(0<(INDIRECT("I"&(2+MATCH(D3,$H$3:$H$7, 0))&":O"&(2+MATCH(D3,$H$3:$H$7,0)))))))

    CTRL+Shift+Enter

    See attached.
    Attached Files Attached Files
    Last edited by kweaver; 2012-08-02 at 18:21.

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Kweaver, i prefered using the second formula, because it covers the array value chnages, but i have two observations, the first one the g5 value is weird for unknown reason to me, all what i did i made the name the same.second thing as i indicated before it has to do match between two values in table 1& 2 and these are the count & the name, the current equation is based on name match. the count value in table 1 is variable. Pls. see attachment. thanks again for your patient with me.
    Attached Files Attached Files
    TIA
    dubdub

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    You had some spaces or blanks in the 5th row in table 2. If you delete them, the calculation is correct: 2017.

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i do not know what is happening i made all the data format identical to where it works but still no sucess,attched, any help in the the two parameters/values match between the two tables i mentioned in my previous post.
    Attached Files Attached Files
    TIA
    dubdub

Page 1 of 2 12 LastLast

Posting Permissions

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