Picking up a range in a formula

  • Hi,


    I need a formula which picks up a range from the table below. What I require is as follows.If in cell G1 I enter the subject name, I need a formula to appear in cell H1 which takes the range of the subject from the table as in the following examples:



    If in G1 I enter maths, H1 should pick up $B$2:$B$9<>""
    If in G1 I enter French, H1 should pick up $D$2:$D$9<>""
    If in G1 I enter Science, H1 should pick up $C$2:$C$9<>""
    ....and so on.



    What I don't want is to generate the formula for cell H1 using the IF function as the range of subjects will increase to a large number over time and the IF function will just become far too long.



    [TABLE="width: 652"]

    [tr]


    [td][/td]


    [TD="align: center"]A[/TD]
    [TD="align: center"]B[/TD]
    [TD="align: center"]C[/TD]
    [TD="align: center"]D[/TD]
    [TD="align: center"]E[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]G[/TD]

    [/tr]


    [tr]


    [TD="align: center"]1[/TD]
    [TD="align: center"]English[/TD]
    [TD="align: center"]Maths[/TD]
    [TD="align: center"]Science[/TD]
    [TD="align: center"]French[/TD]
    [TD="align: center"]History[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Maths[/TD]

    [/tr]


    [tr]


    [TD="align: center"]2[/TD]
    [TD="align: center"]Jack[/TD]
    [TD="align: center"]Jack[/TD]
    [TD="align: center"]Sally[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Sally[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]3[/TD]
    [TD="align: center"]Brandon[/TD]
    [TD="align: center"]Sally[/TD]
    [TD="align: center"]Jack[/TD]
    [TD="align: center"]Sally[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]4[/TD]
    [TD="align: center"]Jenny[/TD]
    [TD="align: center"]Jim[/TD]
    [TD="align: center"]Jim[/TD]
    [TD="align: center"]Jack[/TD]
    [TD="align: center"]Jenny[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]5[/TD]
    [TD="align: center"]Sally[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Jack[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]6[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Jenny[/TD]
    [TD="align: center"]Brandon[/TD]
    [TD="align: center"]Jenny[/TD]
    [TD="align: center"]Brandon[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]7[/TD]
    [TD="align: center"]Jim[/TD]
    [TD="align: center"]Brandon[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Brandon[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]8[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Jim[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]

    [/tr]


    [tr]


    [TD="align: center"]9[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]Jenny[/TD]
    [TD="align: center"][/TD]
    [TD="align: center"]James[/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Picking up a range in a formula


    Highlight the range H1 to H9 and enter the formula =OFFSET(A2,0,MATCH(G1,$A$1:$E$1,0),9,1) and the press Crtl-Shift-Enter (instead of just enter, to create an array formula) to populate the range H1:H9 with the matching results.

  • Re: Picking up a range in a formula


    Thanks for this but how to we get rid of blanks/0 values between the names? Essentially what I require is a list with no gaps.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!