Return cell content in a different column on COUNTIF match

[tjreeddoc]

All,

I need a little help. I have a spreadsheet. In Column E (Capacity MB) it list the size of a virtual disk drive. I used the following formula to find all the disk drives greater than or equal to 300,000

=COUNTIF(E2:E373,">=300,000")

This returned 10 matches. That is great! Now, I need to know the name of the VM which has a disk greater than 300,000. The name of the VM is listed in Column A. But, I don’t know how to find the that information using a function. Help!

Thank you,

tjreeddoc

 

[Peter_SSs]

I tried your formula.

1. It did search D2 – D11
2. It found only one cell (D8) with a value greater than or equal to 150.
3. It returned the content of the corresponding cell in Column A. Cell A8 which has the content of gga

However, it did not continue to search D2 – D11. The criteria has two cells with a value greater than or equal to 150; Cell D8 (150) and D10 (190).

Any thoughts on getting the formula to search all the criteria, finding all cells greater than or equal to 150, and returning all corresponding cells from Column A?

In my test sheet it did find both instances - see the screen shot in my post.

If yours did not, I can think of two possible reasons:
1. You did not copy the formula down to multiple cells, or (more likely)
2. Your cell D10 has something unusual in it and the 190 is not what it appears. In a blank cell put the formula =LEN(D10) and see what it returns. If it is not 3, then go to D10 and manually type in 190 and see what happens.

 

[FDibbins]

I had not tried pete's formula before (I should have), but I just tried it now, on my expanded rang, and it worked just fine and gave the same answers my suggestion did...

	A​	B​	C​	D​	E​	F​	G​
1​	name	size​	​	​	​	index/small​	Index/Aggregate
2​	aaa	10​	​	40​	​	gga	gga
3​	bba	20​	​	bbd	​	iia	iia
4​	cca	30​	​	70​	​	aaaa	aaaa
5​	dda	40​	​	ccd	​
6​	eea	50​	​	110​	​	​
7​	ffa	70​	​	ffd	​	​
8​	gga	80​	​	150​	​	​
9​	hha	90​	​	hhd	​	​
10​	iia	100​	​	190​	​	​
11​	jja	110​	​	jjd
12​	aaaa	120​		175​

 

[tjreeddoc]

Ford,

Thank you for ALL your help!

Placing the following in Cell F2:

=IFERROR(INDEX(A:A,SMALL(IF((ISNUMBER($D$2:$D$12))*($D$2:$D$12>=150),ROW($A$2:$A$12)),ROWS($A$1:A1))),"")
Doing Ctrl+Shift+Enter
then copying down gave me all the Cells between D2 and D12 which are above 150

Thanks,

T.J.

 

[FDibbins]

Happy to help