vlookup help, can this be done? (cells in J&K filled with info from G&I if data in C is a repeat)

Mike Luce

New Member
Joined
Sep 9, 2019
Messages
12
ok, I am sure I was doing something stupidly simple wrong.


but I need to get results based on an entered data point.


example in the spreadsheet:
enter data in C16
search column C for the first repeat of data starting at current entry(C16) if found(C9)
pulls data from column G(G9) and enters it in column J of current entry (J16)
then pull data from column I (I9) and enters it in column K of current entry (K16)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
This is a little confusing. You are trying to find the first appearance of the contents of C16 by looking in column C -- starting WHERE? Starting in C9 and looking DOWN until you get to C16 where the searched-for data is??
 
Upvote 0
Maybe you want this? J16: =INDEX(G1:G15,MATCH(C16,C1:C15,0),1)
And, K16: =INDEX(I1:I15,MATCH(C16,C1:C15,0),1)
 
Upvote 0
This is a little confusing. You are trying to find the first appearance of the contents of C16 by looking in column C -- starting WHERE? Starting in C9 and looking DOWN until you get to C16 where the searched-for data is??

i still have not figured out out to add a screen grab or sample file to the post so i have recreated the data Directly in the post.

This sheet will be a year long record and used to track repeat offenders.

example in the spreadsheet:
enter data in C16
search column C for the first repeat of data starting at current entry(C16) if found(C9)
pulls data from column G(G9) and enters it in column J of current entry (J16)
then pull data from column I (I9) and enters it in column K of current entry (K16)


[TABLE="width: 1379"]
<colgroup><col><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]A
Count[/TD]
[TD]B
Logged by[/TD]
[TD]C
Associate[/TD]
[TD]D
Department[/TD]
[TD]E
Key #[/TD]
[TD]F
Time out[/TD]
[TD]G
Checked Out[/TD]
[TD]H
Returned[/TD]
[TD]I
Report #[/TD]
[TD]J
Last Violation[/TD]
[TD]K
Previous Report#[/TD]
[TD]L
Property[/TD]
[/TR]
[TR]
[TD](row6) [/TD]
[TD]Mike L[/TD]
[TD]Test Subject[/TD]
[TD]hskp[/TD]
[TD]A1[/TD]
[TD]72.00[/TD]
[TD]8/30/19 0:00[/TD]
[TD]9/2/19 0:00[/TD]
[TD]19-IR-000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row7[/TD]
[TD]Mike L[/TD]
[TD]Test Subject[/TD]
[TD]hskp[/TD]
[TD]A2[/TD]
[TD]48.00[/TD]
[TD]9/1/19 0:00[/TD]
[TD]9/3/19 0:00[/TD]
[TD]19-IR-001[/TD]
[TD]8/30/19 0:00[/TD]
[TD]19-IR-0000[/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row8[/TD]
[TD]Mike L[/TD]
[TD]Ron J[/TD]
[TD]hskp[/TD]
[TD]A3[/TD]
[TD]48.00[/TD]
[TD]9/2/19 0:00[/TD]
[TD]9/4/19 0:00[/TD]
[TD]19-IR-002[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row9[/TD]
[TD]Mike L[/TD]
[TD]Kevin S[/TD]
[TD]hskp[/TD]
[TD]A4[/TD]
[TD]48.00[/TD]
[TD]9/3/19 0:00[/TD]
[TD]9/5/19 0:00[/TD]
[TD]19-IR-003[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row10[/TD]
[TD]Mike L[/TD]
[TD]Benda D[/TD]
[TD]hskp[/TD]
[TD]A5[/TD]
[TD]48.00[/TD]
[TD]9/4/19 0:00[/TD]
[TD]9/6/19 0:00[/TD]
[TD]19-IR-004[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row11[/TD]
[TD]Mike L[/TD]
[TD]Mike H[/TD]
[TD]hskp[/TD]
[TD]A6[/TD]
[TD]48.00[/TD]
[TD]9/5/19 0:00[/TD]
[TD]9/7/19 0:00[/TD]
[TD]19-IR-005[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row12[/TD]
[TD]Mike L[/TD]
[TD]Mike K[/TD]
[TD]hskp[/TD]
[TD]A7[/TD]
[TD]48.00[/TD]
[TD]9/6/19 0:00[/TD]
[TD]9/8/19 0:00[/TD]
[TD]19-IR-006[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row13[/TD]
[TD]Mike L[/TD]
[TD]Sammy P[/TD]
[TD]hskp[/TD]
[TD]A8[/TD]
[TD]48.00[/TD]
[TD]9/7/19 0:00[/TD]
[TD]9/9/19 0:00[/TD]
[TD]19-IR-007[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row14[/TD]
[TD]Mike L[/TD]
[TD]George B[/TD]
[TD]hskp[/TD]
[TD]A9[/TD]
[TD]48.00[/TD]
[TD]9/8/19 0:00[/TD]
[TD]9/10/19 0:00[/TD]
[TD]19-IR-008[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row15[/TD]
[TD]Mike L[/TD]
[TD]Ron J[/TD]
[TD]hskp[/TD]
[TD]A10[/TD]
[TD]48.00[/TD]
[TD]9/9/19 0:00[/TD]
[TD]9/11/19 0:00[/TD]
[TD]19-IR-009[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row16[/TD]
[TD]Mike L[/TD]
[TD]Kevin S[/TD]
[TD]hskp[/TD]
[TD]A11[/TD]
[TD]48.00[/TD]
[TD]9/10/19 0:00[/TD]
[TD]9/12/19 0:00[/TD]
[TD]19-IR-010[/TD]
[TD]9/3/19 0:00[/TD]
[TD]19-IR-003[/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row17[/TD]
[TD]Mike L[/TD]
[TD]Benda D[/TD]
[TD]hskp[/TD]
[TD]A12[/TD]
[TD]48.00[/TD]
[TD]9/11/19 0:00[/TD]
[TD]9/13/19 0:00[/TD]
[TD]19-IR-011[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row18[/TD]
[TD]Mike L[/TD]
[TD]Mike H[/TD]
[TD]hskp[/TD]
[TD]A13[/TD]
[TD]48.00[/TD]
[TD]9/12/19 0:00[/TD]
[TD]9/14/19 0:00[/TD]
[TD]19-IR-012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3709[/TD]
[/TR]
[TR]
[TD](row19[/TD]
[TD]Mike L[/TD]
[TD]Ron J[/TD]
[TD]hskp[/TD]
[TD]A14[/TD]
[TD]48.00[/TD]
[TD]9/13/19 0:00[/TD]
[TD]9/15/19 0:00[/TD]
[TD]19-IR-013[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row20[/TD]
[TD]Mike L[/TD]
[TD]Kevin S[/TD]
[TD]hskp[/TD]
[TD]A15[/TD]
[TD]48.00[/TD]
[TD]9/14/19 0:00[/TD]
[TD]9/16/19 0:00[/TD]
[TD]19-IR-014[/TD]
[TD]9/10/19 0:00[/TD]
[TD]19-IR-010[/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row21[/TD]
[TD]Mike L[/TD]
[TD]Benda D[/TD]
[TD]hskp[/TD]
[TD]A16[/TD]
[TD]48.00[/TD]
[TD]9/15/19 0:00[/TD]
[TD]9/17/19 0:00[/TD]
[TD]19-IR-015[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1039[/TD]
[/TR]
[TR]
[TD](row22[/TD]
[TD]Mike L[/TD]
[TD]Kevin S[/TD]
[TD]hskp[/TD]
[TD]A17[/TD]
[TD]96.00[/TD]
[TD]9/16/19 0:00[/TD]
[TD]9/20/19 0:00[/TD]
[TD]19-IR-016[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3709[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I'm guessing, but maybe you want to always be looking back (not always row 16) like this??


Excel 2010
ABCDEFGHIJKL
1CountLogged byAssociateDepartmentKey #Time outChecked OutReturnedReport #Last ViolationPrevious Report#Property
2
3
4
5
6(row6)Mike LTest SubjecthskpA1728/30/2019 0:009/2/2019 0:0019-IR-0003709
7(row7Mike LTest SubjecthskpA2489/1/2019 0:009/3/2019 0:0019-IR-0018/30/2019 0:0019-IR-0003709
8(row8Mike LRon JhskpA3489/2/2019 0:009/4/2019 0:0019-IR-0023709
9(row9Mike LKevin ShskpA4489/3/2019 0:009/5/2019 0:0019-IR-0031039
10(row10Mike LBenda DhskpA5489/4/2019 0:009/6/2019 0:0019-IR-0041039
11(row11Mike LMike HhskpA6489/5/2019 0:009/7/2019 0:0019-IR-0051039
12(row12Mike LMike KhskpA7489/6/2019 0:009/8/2019 0:0019-IR-0061039
13(row13Mike LSammy PhskpA8489/7/2019 0:009/9/2019 0:0019-IR-0073709
14(row14Mike LGeorge BhskpA9489/8/2019 0:009/10/2019 0:0019-IR-0081039
15(row15Mike LRon JhskpA10489/9/2019 0:009/11/2019 0:0019-IR-0099/2/2019 0:0019-IR-0021039
16(row16Mike LKevin ShskpA11489/10/2019 0:009/12/2019 0:0019-IR-0109/3/2019 0:0019-IR-0033709
17(row17Mike LBenda DhskpA12489/11/2019 0:009/13/2019 0:0019-IR-0119/4/2019 0:0019-IR-0041039
18(row18Mike LMike HhskpA13489/12/2019 0:009/14/2019 0:0019-IR-0129/5/2019 0:0019-IR-0053709
19(row19Mike LRon JhskpA14489/13/2019 0:009/15/2019 0:0019-IR-0139/2/2019 0:0019-IR-0021039
20(row20Mike LKevin ShskpA15489/14/2019 0:009/16/2019 0:0019-IR-0149/3/2019 0:0019-IR-0031039
21(row21Mike LBenda DhskpA16489/15/2019 0:009/17/2019 0:0019-IR-0159/4/2019 0:0019-IR-0041039
22(row22Mike LKevin ShskpA17969/16/2019 0:009/20/2019 0:0019-IR-0169/3/2019 0:0019-IR-0033709
Sheet15
Cell Formulas
RangeFormula
J7=IFERROR(INDEX($G$1:G6,MATCH(C7,$C$1:C6,0),1),"")
K7=IFERROR(INDEX($I$1:I6,MATCH(C7,$C$1:C6,0),1),"")

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; mso-bidi-font-size:11.0pt; font-family:"Times New Roman","serif"; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style> <![endif]-->
 
Upvote 0
Why in your example doesn't row 22 pull data from row 20?
 
Upvote 0
Maybe I understand...head scratch! What about:


Excel 2010
ABCDEFGHIJKL
1CountLogged byAssociateDepartmentKey #Time outChecked OutReturnedReport #Last ViolationPrevious Report#Property
2
3
4
5
6(row6)Mike LTest SubjecthskpA1728/30/2019 0:009/2/2019 0:0019-IR-0003709
7(row7Mike LTest SubjecthskpA2489/1/2019 0:009/3/2019 0:0019-IR-0018/30/2019 0:0019-IR-0003709
8(row8Mike LRon JhskpA3489/2/2019 0:009/4/2019 0:0019-IR-0023709
9(row9Mike LKevin ShskpA4489/3/2019 0:009/5/2019 0:0019-IR-0031039
10(row10Mike LBenda DhskpA5489/4/2019 0:009/6/2019 0:0019-IR-0041039
11(row11Mike LMike HhskpA6489/5/2019 0:009/7/2019 0:0019-IR-0051039
12(row12Mike LMike KhskpA7489/6/2019 0:009/8/2019 0:0019-IR-0061039
13(row13Mike LSammy PhskpA8489/7/2019 0:009/9/2019 0:0019-IR-0073709
14(row14Mike LGeorge BhskpA9489/8/2019 0:009/10/2019 0:0019-IR-0081039
15(row15Mike LRon JhskpA10489/9/2019 0:009/11/2019 0:0019-IR-0099/2/2019 0:0019-IR-0021039
16(row16Mike LKevin ShskpA11489/10/2019 0:009/12/2019 0:0019-IR-0109/3/2019 0:0019-IR-0033709
17(row17Mike LBenda DhskpA12489/11/2019 0:009/13/2019 0:0019-IR-0119/4/2019 0:0019-IR-0041039
18(row18Mike LMike HhskpA13489/12/2019 0:009/14/2019 0:0019-IR-0129/5/2019 0:0019-IR-0053709
19(row19Mike LRon JhskpA14489/13/2019 0:009/15/2019 0:0019-IR-0139/9/2019 0:0019-IR-0091039
20(row20Mike LKevin ShskpA15489/14/2019 0:009/16/2019 0:0019-IR-0149/10/2019 0:0019-IR-0101039
21(row21Mike LBenda DhskpA16489/15/2019 0:009/17/2019 0:0019-IR-0159/11/2019 0:0019-IR-0111039
22(row22Mike LKevin ShskpA17969/16/2019 0:009/20/2019 0:0019-IR-0169/14/2019 0:0019-IR-0143709
Sheet15
Cell Formulas
RangeFormula
J7{=IFERROR(INDEX($G$2:G6,MAX((C7=$C$2:C6)*ROW($2:6))-1,1),"")}
K7{=IFERROR(INDEX($I$2:I6,MAX((C7=$C$2:C6)*ROW($2:6))-1,1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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