Return multiple cell if lookup matches a string

mymachix

New Member
Joined
Jul 3, 2019
Messages
2
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....
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Welcome to the forum.

Is this what you're looking for?

Excel 2012
ABCDEFGHIJ
1Server1Server2Server3Server4Server5Server6Servers that are at 70%
25/5/2019528190484946DateServer
35/6/20194135778844705/6/2019Server6
45/7/20194276708745495/7/2019Server3
55/8/20194079879863875/10/2019Server1
65/9/20196533459544955/10/2019Server4
75/10/20197055907035765/12/2019Server6
85/11/2019503436876734
95/12/2019836487847870
105/13/2019645268495556
115/14/2019813867777251

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
I3{=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),"")}
J3{=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)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
It worked!!!!!!!!! i almost cried! i can clean your house or car right now or do your laundry! thank you!!!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top