extract the text based on the => number range

Hi Guys,


I am looking the formula which pick the text on if those text have value 2000 or more than that.

i am using the formula as shown below, but if any text in between have less than 2000 its showing blank. which i am not looking. PLease help me.

=IF('Summed Turnover-ALL'!I7>=2000,'Summed Turnover-ALL'!C7,"")

Data as shown below

Please Login or Register  to view this content.

Using your example...what do you want the formula results to be?

Try this...

Data Range

	A	B	C	D
1	Text	amount	------	Text
2	Pranesh	2,370,657.87		Pranesh
3	anup	2,000.00		anup
4	pradeep	254,154.65		pradeep
5	Shree	18,030.98		Shree
6	london	16,848.22		london
7	america	12,460.60		america
8	brazil	219.95
9	India	758.84

This array formula** entered in D2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$9>=2000,ROW(B$2:B$9)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

The formula needs to be entered as an array formula.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.