Question

So what I have here is two sheets in excel located in the same file.

In column G when the field is changed to "Training", I need the Name, Phone #, and Email that are recorded on this page, to be automatically entered into column A, B, and C on the other excel sheet.

I am working with excel online, thank you for the help.

Phone # Hiring Process Name Email (412)777-4545 [email protected] Waiting on BG (412)777-4545 [email protected] (412)777-4545 [email protected] (412)777-4545 [email protected] (412)777-4545 [email protected] (412)[email protected] James Smith Training John Doe Training Chris Parker Jimmy Dean SHIELD Training Kelly Ott Training Mike Moore(412)777-4545 [email protected] 559 SHIELD (412)777-4545 [email protected] Waiting on DRG (412)777-4545 [email protected] (412)777-4545 [email protected] No Response (412)777-4545 [email protected] (412)777-4545 [email protected] (412)777-4545 [email protected] No Response (412)777-4545 [email protected] (412)777-4545 [email protected] No Response (412)777-4545 [email protected] (412)777-4545 [email protected] Waiting on DRG (412)777-4545 [email protected] (412)777-4545 [email protected] Paperwork (412)777-4545 [email protected] (412)777-4545 [email protected] (412)777-4545 [email protected] Jane Doe Paperwork Mike Trout 567 Sammy Sosa Training Barry Bonds Leaf Erikkson Paperworlk Kevin Knox Jordan Dunn Paperwork 580 Keith Henry 58 Kyle Right Paperwork 584 Austin Smith 585 lylke Chad King SHIELD Brad Drange Paperworlk 588 Steven Tyler Training Jose Valverde Paperwork 012345678901 3-45-6789 01 222222 2-3-3 12345 6789

Answer 1

To demonstrate the formulas, I have create two Excelsheets - "Data" and "Output" as shown below

Output sheet contains the blank table, in which formulas need to be entered.

Phone # Email Hiring Process Name James Smith(412)777-4545 [email protected] Waiting on BG John Doe (412)777-4545 [email protected] Training Chris Parker(412)777-4545 [email protected] Trainin Jimmy Dean(412)777-4545 [email protected] SHIELD (412)777-4545 [email protected] Training im Joe Kelly Ott (412)777-4545 [email protected] Training Mike Moore(412)777-4545sampleemailagmail.com SHIELD (412)777-4545 [email protected] Waiting on DRG Jane Doe Mike Trout (412)777-4545 [email protected] Paperwork 10 Sammy Sosa(412)777-4545 sampleemail @gmail.com No Response Barry Bonds(412)777-4545sampleemailagmail.com Trainin Leaf Erikkson([email protected] Paperwork 13 4 Kevin Knox (412)777-4545 [email protected] No Response 14 ordan Dunn(412)777-4545 [email protected] Paperwork (412)777-4545 [email protected] No Response 16 Keith Henry 1 8 Kyle Right (412)777-4545 [email protected] Paperwork Austin Smith (412)777-4545 [email protected] Waiting on DROG (412)777-4545 [email protected] SHIELD 17 Tvler Ford 19 Chad Kin (412)777-4545 [email protected] Paperwork 0 Brad Drange(412)777-4545 [email protected] Paperwork Steven Tyler (412)777-4545 [email protected] Training Jose Valverde (412)777-4545 [email protected] Paperwork 24 DataOutput

Use the following Array formula, to list all match instances of "Training" in the table in new Excel sheet

To enter the formula, select blank cell A2 for placing the first matched instance, then enter formula =INDEX(Data!$D$2:$D$590,SMALL(IF("Training"=Data!$G$2:$G$590,ROW(Data!$G$2:$G$590)-ROW(Data!$G$2)+1),ROW(1:1))) into the Formula Bar, and then press Ctrl + Shift + Enter simultaneously.

A2 fx HINDEX(Datal$D$2:$D$590,SMALL(IF("Training"-Datal$G$2:$G$590,ROW(Datal$G$2:$G$590)-ROW(DataI$G$2)+1),ROW(1:1)) B. 1 Name Phone # Email 2 John Doe (4121777-4545 sampleemail @gmail.com 3 Chris Parker (412)777-4545 [email protected] 4 Jim Joe (412)777-4545 sampleemail @gmail.com 5 Kelly Ott 412)777-4545 [email protected] 6 Barry Bonds (412)777-4545 [email protected] 7 Steven Tyler (412)777-4545 sampleemail @gmail.com #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! 10| #NUM! #NUM! #NUM! 11| #NUM! #NUM! #NUM! 12| #NUM! #NUM! #NUM! 13| #NUM! #NUM! #NUM! 14 #NUM! #NUM! #NUM! 15 #NUM! ANUM! #NUM! 16| #NUM! #NUM! #NUM! 17| #NUM! #NUM! #NUM! 18| #NUM! #NUM! #NUM! 19 #NUM! #NUM! #NUM! 20| #NUM! #NUM! #NUM! 21| #NUM! #NUM! #NUM! 22| #NUM! #NUM! #NUM! 23| #NUM! #NUM! #NUM! 24 #NUM! #NUM! #NUM! 25| #NUM! #NUM! #NUM! 26| #NUM! #NUM! #NUM! DataOutput

Keep selecting cell A2, and drag the Fill Handle down to get the other matched instances.

Select cell B2 and enter formula =VLOOKUP($A2,Data!$D$1:$F$23,2,0)  (this is a normal formula and not array) and drag the Fill Handle down to copy the formula down

Select cell C2 and enter formula =VLOOKUP($A2,Data!$D$1:$F$23,3,0)  (this is a normal formula and not array) and drag the Fill Handle down to copy the formula down