# 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

### 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 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