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

 

[FDibbins]

If I understand you properly, see if you can adapt this to your needs...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

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

1​

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

40​

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

[tr][td]

2​

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

10​

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

40​

[/td][/tr]

[tr][td]

3​

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

20​

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

40​

[/td][/tr]

[tr][td]

4​

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

30​

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

50​

[/td][/tr]

[tr][td]

5​

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

20​

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

60​

[/td][/tr]

[tr][td]

6​

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

30​

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

[tr][td]

7​

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

40​

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

[tr][td]

8​

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

30​

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

[tr][td]

9​

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

40​

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

[tr][td]

10​

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

50​

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

[tr][td]

11​

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

60​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
D1 would be your criteria
D2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$11>=$D$1,ROW($A$2:$A$11)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Copy down as needed, then copy across

 

[tjreeddoc]

Ford,

Thank you for the quick reply!

With your help, I am almost there!

I took your sample data and tested in a Excel 2016 MSO (16.0.7329.1051) 32-Bit.
Following is the formula I used:
=IFERROR(INDEX(A:A,SMALL(IF($D$2:$D$11>="150",ROW($A$2:$A$11)),ROWS($A$1:A1))),"")

And, I used Ctrl+Shift+Enter to convert the formula to an Array.

Here is an HTML paste of my test spreadsheet:

[TABLE="width: 384"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bba[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]bbd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cca[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dda[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD]ccd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]eea[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ffa[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD]ffd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]gga[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hha[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD]hhd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]iia[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jja[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD]jjd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name >=150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bba[/TD]
[/TR]
</tbody>[/TABLE]


While the formula kind of worked, it only found one cell in the criteria that was above or equal to 150.
It returned the content of Cell A3 (bba). This is not exactly what I am looking for.

I was expecting the formula to:
1. Search through D2 through D11
2. Find all Cells in D2 through D11 which have a value above or equal to 150. (There are two Cells between D2 and D11 which are greater than or equal to 150.)
3. Return the content of corresponding Cells in Column A. (Cell A8 (gga) and Cell A10 (iia))

Any thoughts?

TJ

 

[FDibbins]

You need to remove the "" from the 150, that is making it text

 

[Peter_SSs]

You need to remove the "" from the 150, that is making it text

I think there is more than that as all the text values in column D will also meet the >=150 criteria.

@tjreeddoc
Here is another version that you could try. This does not require the array entry.

Book1
	A	B	C	D	E	F
1	name	size
2	aaa	10		40		gga
3	bba	20		bbd		iia
4	cca	30		70
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
List

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW(D$2:D$11)-ROW(D$2)+1)/(D$2:D$11+0>=150),ROWS(F$2:F2))),"")

 

[tjreeddoc]

Ford,

Thanks for the suggestion of removing "" from 150. However, that suggestion did not work. It still returns the content of Cell A3 (bba).

T.J.

 

[tjreeddoc]

Peter_SSs

Thank you for jumping in this Thread!

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?

[TABLE="width: 448"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[TD]aaf[/TD]
[/TR]
[TR]
[TD]bba[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]bbd[/TD]
[TD][/TD]
[TD]bbf[/TD]
[/TR]
[TR]
[TD]cca[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD]ccf[/TD]
[/TR]
[TR]
[TD]dda[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD]ccd[/TD]
[TD][/TD]
[TD]ddf[/TD]
[/TR]
[TR]
[TD]eea[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD]eef[/TD]
[/TR]
[TR]
[TD]ffa[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD]ffd[/TD]
[TD][/TD]
[TD]fff[/TD]
[/TR]
[TR]
[TD]gga[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD]ggf[/TD]
[/TR]
[TR]
[TD]hha[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD]hhd[/TD]
[TD][/TD]
[TD]hhf[/TD]
[/TR]
[TR]
[TD]iia[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[TD]iif[/TD]
[/TR]
[TR]
[TD]jja[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD]jjd[/TD]
[TD][/TD]
[TD]jjf[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name >=150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bba[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]gga[/TD]
[/TR]
</tbody>[/TABLE]

T.J.

 

[FDibbins]

This seems to work, based on Pete's example data
=IFERROR(INDEX(A:A,SMALL(IF((ISNUMBER($D$2:$D$11))*($D$2:$D$11>=150),ROW($A$2:$A$11)),ROWS($A$1:A1))),"")
ARRAY entered

 

[tjreeddoc]

Ford,

Thanks again for the post.

Once you changed Peter's example, were you able to search and find both values greater than 150?
When I tried your change to Peter's example, it still only found the Cell D8 (150). It still does not find Cell D10 (190).
And yes, I entered =IFERROR(INDEX(A:A,SMALL(IF((ISNUMBER($D$2:$D$11))*($D$2:$D$11>=150),ROW($A$2:$A$11)),ROWS($A$1:A1))),"") as an Array (Ctrl+Shift+Enter).

Any thoughts?

T.J.

 

[FDibbins]

Yes I was able to find the 2 entries, I added an extra row, just to test it again
[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][/tr]
[tr][td]

1​

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

size​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

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

10​

[/td][td]

​

[/td][td]

40​

[/td][td]

​

[/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][/tr]

[tr][td]

4​

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

30​

[/td][td]

​

[/td][td]

70​

[/td][td]

​

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

[tr][td]

5​

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

40​

[/td][td]

​

[/td][td]ccd[/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][/tr]

[tr][td]

7​

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

70​

[/td][td]

​

[/td][td]ffd[/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][/tr]

[tr][td]

9​

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

90​

[/td][td]

​

[/td][td]hhd[/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][/tr]

[tr][td]

11​

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

110​

[/td][td]

​

[/td][td]jjd[/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][/tr]
[/table]
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))),"")
CSE
copy down
I see no difference between your formula and mine (other than I increased my range by 1)
Mine=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))),"")
Yours=IFERROR(INDEX(A:A,SMALL(IF((ISNUMBER($D$2:$D$11))*($D$2:$D$11>=150),ROW($A$2:$A$11)),ROWS($A$1:A1))),"")