v Lookup series of data ranging + and - 100

[scottlarock]

Good day excel brothers, seniors & juniors

let's get str8 to the point.
My data sheet comprises of projects in MW. So I have the name of a project and there power in MW.
I would like to fetch all projects that have a similar MW power within a range of + and - 100, and align them one under another.
i.e.:

project 1 = 1000 MW; year=2007
project 2 = 1200 MW; year=2008
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
project 5 = 800 MW;year=2009

I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :

project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009

I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.
Tons of thanks in advance !!

 

[Domenic]

If I understand you correctly, 'project 1 = 1000 MW' is entered in one cell, and 'year=2007' is entered in another cell. Same thing for the rest of them. So, in essence, they would be entered in a range of cells such as A2:B6. Is this correct?

 

[scottlarock]

Well, actually, it goes like this :

project 1 = 1000 MW; year=2007
A1 B1 C1
project 2 = 1200 MW; year=2008
A2 B2 C2
project 3 = 900 MW; year=2008
A3 B3 C3
project 4 = 1100 MW;year=2009
A4 B4 C4
project 5 = 800 MW;year=2009
A5 B5 C5

I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :

project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009

I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.
Tons of thanks in advance !!

 

[ddadmin2009]

Check the below article for the solution.

http://funwithexcel.blogspot.com/2009/04/getting-data-between-two-ranges.html

Hope this helps. Let me know if you have any other questions.

 

[scottlarock]

Thanks for the input, I like the 'text to columns' trick. But I don't need it.

I'll try to explain the problematic more thoroughly.
On one sheet, I have a database with a list of projects, each project has various atributes such as name, power in MW, year, price etc...we will call this sheet 2.

On an other sheet, I have a free space for listing 10 of those projects which resemble a given condition which we will consider to be 1000 MW. We will call this sheet 1.

I need to portray all projects in sheet 1 which are between 900 MW & 1100 MW.

Let us firstly assume that there are 10 or less projects that meet the above criteria so they could all fit in the provided space.

They should be portrayed one under the other with no free space in between the lines.

To take it back to my example, it is essentially a vlookup function that will understand to fetch 'Project 1', and in the line underneath, fetch 'Project 3', and in the line underneath, fetch 'Project 4'. Instead of the classic vLookup that will repeatedly reflect project 1, project 1, project 1.

Data from Sheet 2 :

A1='Project 1';B1='1000';C1='2007'
A2='Project 2';B2='1200';C2='2008'
A3='Project 3';B3='900';C3='2008'
A4='Project 4';B4='1100';C4='2009'
A5='Project 5';B5='800';C5='2009'

Formula that reflects all projects + and - 100 MW for criteria '1000' :
Data on free space provided on Sheet 1 :

'sheet 2'!A1;'sheet 2'!B1;'sheet 2'!C1
'sheet 2'!A3;'sheet 2'!B3;'sheet 2'!C3
'sheet 2'!A4;'sheet 2'!B4;'sheet 2'!C4

If you didnt understand I will try again, it's not easy to explain without pictures

 

[Domenic]

On Sheet1, let A2 contain the target value/criteria, such as 1000. Then try...

B2:

=SUMPRODUCT(--(ABS(Sheet2!B1:B5-A2)<=100))

C2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX(Sheet2!A$1:A$5,SMALL(IF(ABS(Sheet2!$B$1:$B$5-$A$2)<=100,ROW(Sheet2!A$1:A$5)-ROW(Sheet2!A$1)+1),ROWS(C$2:C2))),"")

 

[Beate Schmitz]

Hello Scottlarock,

why don't you use the advanced filter?

Example:

Excel Workbook
	A	B	C	D	E	F	G
1	Project	MW	Year	*	Criteria MW +/- 100:	1.000	*
2	Project1	1.000	2007	*	*	*	*
3	Project2	1.200	2008	*	MW	MW	*
4	Project3	900	2008	*	>=900		*
5	Project4	1.100	2009	*	*	*	*
6	Project5	800	2009	*	Filter Results:	*	*
7	*	*	*	*	Project	MW	Year
8	*	*	*	*	Project1	1.000	2007
9	*	*	*	*	Project3	900	2008
10	*	*	*	*	Project4	1.100	2009
11	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	F1	value, you are searching for +/-100	*	*	*	*	*
14	A1:C6	Listrange	*	*	*	*	*
15	E3:F4	Criteriarange (observe the formulas!)	*	*	*	*	*
16	E7	Copy to	*	*	*	*	*
Sheet

I assume, you speak german too. To learn more about advanced filter pls refer to:

<table border="0" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:352px;" /></colgroup><tr style="height:24px ;"><td style="font-weight:bold; ">Guckst du:</td></tr><tr style="height:27px ;"><td style="color:#0000ff; text-decoration:underline; "><a href="http://www.online-excel.de/excel/singsel.php?f=6" target="_blank">Spezialfilter (1) - Duplikate wegfiltern</a></td></tr><tr style="height:27px ;"><td style="color:#0000ff; text-decoration:underline; "><a href="http://www.online-excel.de/excel/singsel.php?f=5" target="_blank">Spezialfilter (2) - Filtern nach Kriterien</a></td></tr><tr style="height:27px ;"><td style="color:#0000ff; text-decoration:underline; "><a href="http://www.online-excel.de/excel/singsel.php?f=7" target="_blank">Spezialfilter (3) - Filtern mit Funkionen</a></td></tr><tr style="height:27px ;"><td style="color:#0000ff; text-decoration:underline; "><a href="http://www.online-excel.de/excel/singsel.php?f=8" target="_blank">Spezialfilter (4) - Filterergebnis in ein anderes Tabellenblatt</a></td></tr></table>

Pay special attention to "Spezialfilter (4) - Filterergebnis in ein anderes Tabellenblatt".

 

[scottlarock]

wow ! You are truely a Master
However, I only get the first line right, the rest of the lines give me an error "#NUM". I can't figure out what is wrong.
Here are the exact formulas that I am using :
1000 is my criteria, and my database is on 'sheet 2' from A2 to C11, it is layed out with project name on A column, power MW in B column, year in C column.

A2=1000

B2=SUMPRODUCT(--(ABS('sheet 2'!B2:B11-A2)<=100))

C2=IF(ROWS(C$2:C2)<=$B$2;INDEX('sheet 2'!A$2:A$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!A$2:A$11)-ROW('sheet 2'!A$2)+1);ROWS(C$2:C2)));"")

D2=IF(ROWS(D$2:D2)<=$B$2;INDEX('sheet 2'!B$2:B$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!B$2:B$11)-ROW('sheet 2'!B$2)+1);ROWS(D$2:D2)));"")

E2=IF(ROWS(E$2:E2)<=$B$2;INDEX('sheet 2'!C$2:C$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!C$2:C$11)-ROW('sheet 2'!C$2)+1);ROWS(E$2:E2)));"")

C3=IF(ROWS(C$2:C3)<=$B$2;INDEX('sheet 2'!A$2:A$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!A$2:A$11)-ROW('sheet 2'!A$2)+1);ROWS(C$2:C3)));"")

D3=IF(ROWS(D$2:D3)<=$B$2;INDEX('sheet 2'!B$2:B$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!B$2:B$11)-ROW('sheet 2'!B$2)+1);ROWS(D$2:D3)));"")

E3=IF(ROWS(E$2:E3)<=$B$2;INDEX('sheet 2'!C$2:C$11;SMALL(IF(ABS('sheet 2'!$B$2:$B$11-$A$2)<=100;ROW('sheet 2'!C$2:C$11)-ROW('sheet 2'!C$2)+1);ROWS(E$2:E3)));"")

I hope I gave you sufficient info.
Many thanks in advance and ofcourse...happy easter !

 

[Domenic]

The second formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

And, a Happy Easter to you too...

 

[Beate Schmitz]

Hello Scottlarock,

haven't you confirmed the formula with CONTROL+SHIFT+ENTER?