Results 1 to 10 of 10

Thread: Grab corresponding value from a data range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Grab corresponding value from a data range

    My Data Range is B4:L10 consisting of 5 columns (B,E,H,J & L)as shown
    plus
    B2, E2, H2, J2 & L2 is either of 1 or 0

    Output required in N4:N10 as
    ‘grab’ the value from the corresponding row cell of B4:L10 which has 1 in B2:L2.

    How to accomplish?
    Thanks in advance
    I am using Excel 2007
    Sheet1

    B C D E F G H I J K L M N
    2 0 0 1 0 0
    3
    4 45 48 66 77 890 66
    5 78 85 90 777 888 90
    6
    7
    8
    9
    10 2 4 5 18 2 5

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,454
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Grab corresponding value from a data range

    Copy N4 down:
    Sheet4

    BCDEFGHIJKLMN
    20 0 1 0 0
    3
    445 48 66 77 890 66
    578 85 90 777 888 90

    Spreadsheet Formulas
    CellFormula
    N4=SUMPRODUCT($B$2:$L$2,B4:L4)


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    In N4, fill down:
    =IF(COUNTA(B4:L4)<>0,OFFSET(A4,0,MATCH(1,$B$2:$L$2,0)),"")

    This could be dressed up a bit to check if the value was "" rather than the whole row.
    Last edited by Kenneth Hobson; Sep 22nd, 2019 at 11:28 PM.

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    No this is not required. Probably I missed a lot….please bear…

    My data is in more than 1 range like
    Range 1: C4:H100 having a ‘Commander cell’ B2 which is EITHER of 1 or 0 PLUS ‘Sub-Commander cells’ C3:H3 which have values
    Range 2: M4:R100 having a ‘Commander cell’ L2 which is EITHER of 1 or 0 PLUS ‘Sub-Commander cells’ M3:R3 which have values

    At a time ONLY 1 ‘Commander cell’ would be 1

    Output is required in U4:U100 as
    ‘Grab’ value from cell which MATCHES EXACTLY U3 with ‘Sub-Commander cells’ HAVING ‘Commander cell’ as 1
    Sheet1

    B C D E F G H I J K L M N O P Q R S T U
    2 0 1
    3 4 5 6 7 8 9 4 5 6 7 8 9 4
    4 77 88 55 32 50 100 87 12 5 8 9 10 87

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    Please allow me to add more conditions to my post#4

    There are 10 Ranges having a ‘Commander cell’ (which is EITHER of 1 or 0) PLUS ‘Sub-Commander cells’ (which have values)

    Each of the TEN ranges can be ‘identified’ for output through values like A,B,C,D,E,F,G,H,I & J in C1:H1; M1:R1; & so on
    i.e.

    C1=A, D1=A, E1=A, F1=A, G1=A, H1=A
    M1=B, N1=B, O1=B, P1=B, Q1=B, R1=B

    Feel free to ask for more clarifications

  6. #6
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    You lost me. If something changed, then post another picture that might help.

    You can use a tool from here to make things more clear maybe. https://www.mrexcel.com/forum/about-...tachments.html

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    [QUOTE=Kenneth Hobson;5347419]You lost me.

    I’ll try to make it SIMPLEST

    Data Range B4:I10 containing values
    B3:I3 contains a ‘header’ (A, B, C or D)
    B2:I2 is EITHER of 1 or 0

    Output is required in K4:N10 as

    ‘grab’ values from the corresponding row of B4:I10 where K3:N3 MATCHES EXACTLY with B3:I3 HAVING B2:I2 as 1
    Sheet1

    B C D E F G H I J K L M N
    2 1 1 1 1 0 0 0 0
    3 A B C D A B C D A B C D
    4 55.00 48.00 77.00 88.00 100.00 200.00 300.00 400.00 55.00 48.00 77.00 88.00
    5
    6
    7
    8
    9
    10 100.00 200.00 300.00 400.00 500.00 600.00 700.00 800.00 100.00 200.00 300.00 400.00

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,639
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Grab corresponding value from a data range

    Try:

    ABCDEFGHIJKLMN
    1211110000
    23ABCDABCDABCD
    345548778810020030040055487788
    45
    56
    67
    78
    89
    910100200300400500600700800100200300400

    Sheet7



    Worksheet Formulas
    CellFormula
    K3=IF(B3="","",SUMIFS($B3:$I3,$B$1:$I$1,1,$B$2:$I$2,K$2))



    Put the formula in K3, then copy to the right and down as needed.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grab corresponding value from a data range

    [QUOTE=Eric W;5347710]Try:

    Thanks Eric. IT WORKS.

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,639
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Grab corresponding value from a data range

    Glad we could help.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

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
  •