How to lookup and sort rows based on the values in cells

[sudharshan86]

Hello,

I have some values in a column, like 1000, 2000, 3000, 4000 and so on, in one excel.

And i have another excel with some numbers, counts and comments.

In that comments, numbers in the first excel will be mentioned somewhere in the whole statement.

eg: this is a 2000 example

Now i need to bring all the rows together which are all having 2000 in their comments and its the same for others too.

The code i have got is

=LOOKUP(2, 1/(ISNUMBER(SEARCH('[Excel One.xlsx]Sheet1'!$A$2:$A$12, B2))), '[Excel One.xlsx]Sheet1'!$A$2:$A$12)

but it gives the value #N/A if the numbers are not available in 'Excel One'.

If, in 'Excel one' i have only even numbers say 2000, 3000,.... but in the 'Excel two' i have few numbers like 2200, 3400,... so this numbers are in between 2000 to 3000 and it should also need to be found and sorted.

Eg:
2000
2200
3000
3400

is there a way to do this.

Can anyone help me if it is possible to do.