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

 

[scottlarock]

Domenic,
The next step to my perfect formula ...

Still using option 1 of the example, I have another criteria, say A3=Yes or A3=No
and Column D header in Sheet 2 is called "Yes or No", so :

'Sheet 2'!D2=Yes
'Sheet 2'!D3=Yes
'Sheet 2'!D4=No
'Sheet 2'!D5=Yes
'Sheet 2'!D6=No

I would need to perform the same exact thing as you have helped me achieve previously, except that I would need to portray the projects that are 'Yes' when A3=Yes, or the projects that are 'No' when A3=No

Hoping this explanation made sense.
As per usual...Tons of thanks in advance

 

[scottlarock]

Below is a more detailed explanation of what I am working with in case the comment above about the 'new criteria' being Yes or No was not very clear.

So how would that new condition be added to the formula(s) of 'Sheet 1' ?

The formulas today are the still the following :

Criteria

A2 =MIN(15;SUMPRODUCT(--(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100)))

The new criteria in Sheet 1 is : A3 =Yes

B2 =1000

Results (confirmed & copied accross & down)

C2 ={IF(ROWS(C$2:C2)<=$A$2;INDEX('Sheet 2'!A$2:A$100;SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100;ROW('Sheet 2'!$B$2:$B$100));$A$2);IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100;ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1));ROWS(C$2:C2)));"")}

Sheet 2 database
'Sheet 2'!A2:A100 are project names
'Sheet 2'!B2:B100 is the power of the project in MW ie:1000
'Sheet 2'!C2:C100 are the years of the project ie: 2009

The new criteria column in the db is :

'Sheet 2'!D2:D100 is Yes or No ie : Yes

Hoping this was clear
Tons of thanks in advance.

 

[Domenic]

Below is a more detailed explanation of what I am working with in case the comment above about the 'new criteria' being Yes or No was not very clear.

Actually, it was very clear. I replied yesterday, yet for some reason I don't see it posted.

So how would that new condition be added to the formula(s) of 'Sheet 1' ?

A2:

=MIN(15,SUMPRODUCT(--(ABS('Sheet 2'!B2:B100-B2)<=100),--('Sheet 2'!D2:D100=Sheet1!A3)))

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

=IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,IF('Sheet 2'!$D$2:$D$100=$A$3,ROW('Sheet 2'!$B$2:$B$100))),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,IF('Sheet 2'!$D$2:$D$100=$A$3,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1))),ROWS(C$2:C2))),"")

 

[scottlarock]

Domenic,
Awesome !
So what if cell A3=ALL instead of Yes or No, thus the Yes or No filter wouldn't apply, meaning the project selection from the database on Sheet 2 only relies on selecting the last 15 projects or less that correspond to the power in cell B2 ?

Hoping the formula is feasible.
Thanks Dominic !

How would that change the formula(s)

 

[Domenic]

Try...

A2:

=MIN(15,SUMPRODUCT(--(ABS('Sheet 2'!B2:B100-B2)<=100),('Sheet 2'!D2:D100=Sheet1!A3)+(A3="All")))

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

=IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,IF(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All"),ROW('Sheet 2'!$B$2:$B$100))),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,IF(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All"),ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1))),ROWS(C$2:C2))),"")

 

[scottlarock]

Honestly...you're a genius Domenic !
Thanks a million !
have a wonderful Saturday out there in Canada !!

 

[Domenic]

Thanks a million !

You're very welcome!

have a wonderful Saturday out there in Canada !!

Thanks... you too!

 

[scottlarock]

Domenic,
I'm afraid I can't seem to add a criteria to the formula..once I understand how to add a new one then I will be able to add a third if needed.

Say in cell A4, we have a product name called 'GT' or 'ST'.
Depending on what is selected in cell A4, certain projects would be selected.

So, up to now before adding the criteria in cell A4, we have the following formulas :

Criteria

A2 =MIN(15;SUMPRODUCT(--(ABS('Sheet 2'!B2:B100-B2)<=100);('Sheet 2'!D2:D100=A3)+(A3="All")))

A3 ='Yes';'No';'All'

A4 ='GT;'ST';'All' --> we could write 'All products' if 2 'All' is confusing for excel...

Results (confirmed & copied accross & down)

C2 ={IF(ROWS(C$2:C2)<=$A$2;INDEX('Sheet 2'!A$2:A$100;SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100;IF(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All");ROW('Sheet 2'!$B$2:$B$100)));$A$2);IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100;IF(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All");ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)));ROWS(C$2:C2)));"")}

Sheet 2 database
'Sheet 2'!A2:A100 are project names
'Sheet 2'!B2:B100 is the power of the project in MW ie:1000
'Sheet 2'!C2:C100 are the years of the project ie: 2009
'Sheet 2'!D2:D100 is Yes or No ie : Yes

The new criteria column in the db is :

'Sheet 2'!E2:E100 is GT or ST ie : GT

by the way : is there a limit to the length of the formulas or not really, I think I read somewhere that we can only add 6 criteria or something of the sort... ?

I hope you can help me out again with this one.
Many thanks in advance domenic.
Enjoy your day in CA

 

[Domenic]

Yes, there are limits to the length of a formula and to the number of functions that can be nested. Also, array formulas take longer to calculate, especially if the size of the range being referenced is large. And the more conditions one adds to an array formula, the more time it takes to calculate. For these reasons, it would probably be more efficient to filter the data for the desired results. To continue with a formula approach, try...

A2:

=MIN(15,SUMPRODUCT(--(ABS('Sheet 2'!B2:B100-B2)<=100),('Sheet 2'!D2:D100=Sheet1!A3)+(A3="All"),('Sheet 2'!E2:E100=A4)+(A4="All")))

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

=IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF((ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100)*(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All"))*(('Sheet 2'!$E$2:$E$100=$A$4)+($A$4="All")),ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF((ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100)*(('Sheet 2'!$D$2:$D$100=$A$3)+($A$3="All"))*(('Sheet 2'!$E$2:$E$100=$A$4)+($A$4="All")),ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2))),"")

 

[scottlarock]

Thanks Boss ! Works perfect as per usual..

So filtering teh data do you mean using the 'Advanced Filter' ?

As for the last formula you gave me, how many more criteria do you think I could fit in considering circa 200 lines in the 'Sheet 2' database.. ?

If I don't add anymore criteria...would that last formula work with a circa 500 line db ?

I know the above questions are subject to relative circumstances...but maybe you have an idea of what the potential is...

Have a nice day.
Merci beaucoup chef !