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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]name[/td][td]

size​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

index/small​

[/td][td]Index/Aggregate[/td][/tr]

[tr][td]

2​

[/td][td]aaa[/td][td]

10​

[/td][td]

​

[/td][td]

40​

[/td][td]

​

[/td][td]gga[/td][td]gga[/td][/tr]

[tr][td]

3​

[/td][td]bba[/td][td]

20​

[/td][td]

​

[/td][td]bbd[/td][td]

​

[/td][td]iia[/td][td]iia[/td][/tr]

[tr][td]

4​

[/td][td]cca[/td][td]

30​

[/td][td]

​

[/td][td]

70​

[/td][td]

​

[/td][td]aaaa[/td][td]aaaa[/td][/tr]

[tr][td]

5​

[/td][td]dda[/td][td]

40​

[/td][td]

​

[/td][td]ccd[/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]eea[/td][td]

50​

[/td][td]

​

[/td][td]

110​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]ffa[/td][td]

70​

[/td][td]

​

[/td][td]ffd[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]gga[/td][td]

80​

[/td][td]

​

[/td][td]

150​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]hha[/td][td]

90​

[/td][td]

​

[/td][td]hhd[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]iia[/td][td]

100​

[/td][td]

​

[/td][td]

190​

[/td][td]

​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]jja[/td][td]

110​

[/td][td]

​

[/td][td]jjd[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]aaaa[/td][td]

120​

[/td][td][/td][td]

175​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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