none
Formula required for getting range and its related column for number

    Question

  • i have a file of ranges like this in one sheet

    CityEnglish

    Area

    Range Start

    Range End

    Range

    A

    A2

    042200000

    042200999

    042200000 - 042200999

    B

    B2

    042201000

    042201099

    042201000 - 042201099

    C

    C2

    042201100

    042201199

    042201100 - 042201199

    D

    D2

    042201200

    042201299

    042201200 - 042201299

    E

    E2

    042201300

    042201399

    042201300 - 042201399

    my second sheet have numbers

    Number

     

    Range

    CityEnglish

    Area

    42201000

    42201087

    42201301

    i want result like this

    Number

    Range

    CityEnglish

    Area

    42201000

    042201000 - 042201099

    B

    B2

    42201087

    042201000 - 042201099

    B

    B2

    42201301

    042201300 - 042201399

    E

    E2

    Monday, November 26, 2012 8:09 AM

Answers

  • Hi,

    First, in sheet1, copy Range column to the first column.

    I have written a macro to return the Range column.

    The code is below: (Change the number "5" in For loop code to the number of the data in Sheet1.)

    Function return_range(num As Long) As String
    Dim loop_max As Integer

    'Change the loop times to the number of you data number.

    For loop_max = 1 To 5
        If num <= Sheets(1).Cells(loop_max + 1, 4) Then
            If num >= Sheets(1).Cells(loop_max + 1, 3) Then
                return_range = Sheets(1).Cells(loop_max + 1, 5)
            End If
        End If
       
    Next
    End Function

    Then in the Column Range in Sheet2, use the function as below:

    =return_range(A2)

    Then use this function to get CityEnglish:

    =VLOOKUP(B2,Sheet1!$A$1:$D$6,2,FALSE

    Use this function to get Area:

    =VLOOKUP(B2,Sheet1!$A$2:$E$6,3,FALSE)


    Jaynet Zhang
    TechNet Community Support

    Tuesday, November 27, 2012 2:48 PM
    Moderator

All replies

  • WaqasF wrote:

    i have a file of ranges like this in one sheet

    CityEnglish

    Area

    Range Start

    Range End

    Range

    A

    A2

    042200000

    042200999

    042200000 - 042200999

    B

    B2

    042201000

    042201099

    042201000 - 042201099

    C

    C2

    042201100

    042201199

    042201100 - 042201199

    D

    D2

    042201200

    042201299

    042201200 - 042201299

    E

    E2

    042201300

    042201399

    042201300 - 042201399

    [....]
    my second sheet have numbers [....] i want result like this

    Number

    Range

    CityEnglish

    Area

    42201000

    042201000 - 042201099

    B

    B2

    42201087

    042201000 - 042201099

    B

    B2

    42201301

    042201300 - 042201399

    E

    E2

     

    You could probably set up a pivot table.  But to answer your question directly, the formulas in the second table could be (assuming Number is column A starting in row 2):

    Range:

    =INDEX(Sheet1!$E$2:$E$5,MATCH(A2,Sheet1!$C$2:$C$5,1))

    CityEnglish:

    =INDEX(Sheet1!$A$2:$A$5,MATCH(A2,Sheet1!$C$2:$C$5,1))

    Area:

    =INDEX(Sheet1!$B$2:$B$5,MATCH(A2,Sheet1!$C$2:$C$5,1))



    • Edited by joeu2004 Monday, November 26, 2012 8:49 AM
    Monday, November 26, 2012 8:47 AM
  • Can you please show me how i placed pivot table to get the same result

    Thanks

    Monday, November 26, 2012 10:10 AM
  • Hi

    You could use a simple VLOOKUP however the table structure is a problem as this function searches the left most column and in your example City and Area are to the left of the Range Start.

    Lets assume your tables starts in A1.  If you can add two columns to the right of the Table with simple formulae

    F2: =A2

    G2: =B2

    copied down.

    Then you could use the formula below:

    =VLOOKUP(A2,Sheet1!$C$2:$G$6,3,TRUE)

    For determining the Range column on sheet2.  The true forces the Vlookuo to perform an approximate match, so the nearest match that is not higher than the value being looked up.

    To get the other columns just use the same formulae and change the column number by adding one for each column to the right.

    If you cannot add the two columns then obviously this method will not work.  If your main problem is the duplication of columns then it is possible to hide columns F and G to retain your original layout.

    Hope this helps


    G North MCT

    Monday, November 26, 2012 11:09 AM
  • Hi,

    First, in sheet1, copy Range column to the first column.

    I have written a macro to return the Range column.

    The code is below: (Change the number "5" in For loop code to the number of the data in Sheet1.)

    Function return_range(num As Long) As String
    Dim loop_max As Integer

    'Change the loop times to the number of you data number.

    For loop_max = 1 To 5
        If num <= Sheets(1).Cells(loop_max + 1, 4) Then
            If num >= Sheets(1).Cells(loop_max + 1, 3) Then
                return_range = Sheets(1).Cells(loop_max + 1, 5)
            End If
        End If
       
    Next
    End Function

    Then in the Column Range in Sheet2, use the function as below:

    =return_range(A2)

    Then use this function to get CityEnglish:

    =VLOOKUP(B2,Sheet1!$A$1:$D$6,2,FALSE

    Use this function to get Area:

    =VLOOKUP(B2,Sheet1!$A$2:$E$6,3,FALSE)


    Jaynet Zhang
    TechNet Community Support

    Tuesday, November 27, 2012 2:48 PM
    Moderator