Return multiple cell if lookup matches a string

[mymachix]

Hi,

I've been searching for 3 days now for the below requirement but couldn't find an answer. i tried combining multiple VLOOKUP formulas but no luck.

i have big data below that is used to forecast when a server will reach memory consumption of 70%

B1 to IC1 list of Server names. A2 to A330 are dates (5/15/2019 to 2/20/2020). B2 to IC330 are 2 digit numbers, increasing from 33-99 where it tells when a server will reach 70% on specific date.


i want to collect only those cells with value of 70 and return with it the date from column A <same row> and Server name from the top header <same column>

is this possible?? please help....

 

[Eric W]

Welcome to the forum.

Is this what you're looking for?

Excel 2012

	A	B	C	D	E	F	G	H	I	J
Server1	Server2	Server3	Server4	Server5	Server6	Servers that are at 70%
Date	Server
Server6
Server3
Server1
Server4
Server6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5/5/2019[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5/6/2019[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/6/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/7/2019[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/7/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5/8/2019[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/10/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/9/2019[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/10/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5/10/2019[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/12/2019[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5/11/2019[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]5/12/2019[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5/13/2019[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5/14/2019[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]{=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$G$11=70,ROW($B$2:$G$11)*1000+COLUMN($B$2:$G$11)),ROWS($I$3:$I3))/1000),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]{=IFERROR(INDEX($1:$1,MOD(SMALL(IF($B$2:$G$11=70,ROW($B$2:$G$11)*1000+COLUMN($B$2:$G$11)),ROWS($I$3:$I3)),1000)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[mymachix]

It worked!!!!!!!!! i almost cried! i can clean your house or car right now or do your laundry! thank you!!!!!!!!

 

[Eric W]

Well, I am about ready to go do some weeding . . .

Glad I could help!