Match value from a specific cell with the same value in multiple columns, return the results of these rows from a specific column

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
22
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good day,
I have been trying to clean up my spread sheet by removing helper columns and can't figure out how o do it.
I have a main log with all of my data and another sheet as a request. I am trying to populate the request based on the contents in a specific cell matching the same contents on another sheet over multiple columns, then returning the data from another column. I would like to do this with formulas and not VBA. My original spreadsheet has 10 helper columns that I need to get rid of; other people keep deleting the contents and adding rows, not knowing the formulas are there.

ABCDEFGHI
1Data
2
3Helper
4DrawingLine#Person6amNoon6pmMidnight
5Iso 54waterBill
61Iso 54gasFrankRequest 1
7Iso 52nitrogenDave
82Iso 54gasDaveRequest 1
9Iso 54nitrogenFrank
103Iso 54waterFrankRequest 1
11Iso 54waterFrank
12Iso 52nitrogenFrank
13Iso 53nitrogenDave
144Iso 54nitrogenBillRequest 1
15Iso 53nitrogenDave
16Iso 53waterBill
175Iso 53waterBillRequest 1
18Iso 54gasDave
19Iso 52gasBill
20Iso 52nitrogenBill
216Iso 54gasFrankRequest 1
22Iso 53gasDave
23Iso 53waterBill
24Iso 54nitrogenFrank
257Iso 52waterDaveRequest 1
26Iso 54waterBill

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Log

Worksheet Formulas
CellFormula
A5=IF(OR(F5=Request!F1,G5=Request!F1,H5=Request!F1,I5=Request!F1),1,"")
A6=IF(OR(F6=Request!$F$1,G6=Request!$F$1,H6=Request!$F$1,I6=Request!$F$1),MAX($A$5:$A5)+1,"")
A7=IF(OR(F7=Request!$F$1,G7=Request!$F$1,H7=Request!$F$1,I7=Request!$F$1),MAX($A$5:$A6)+1,"")
A8=IF(OR(F8=Request!$F$1,G8=Request!$F$1,H8=Request!$F$1,I8=Request!$F$1),MAX($A$5:$A7)+1,"")
A9=IF(OR(F9=Request!$F$1,G9=Request!$F$1,H9=Request!$F$1,I9=Request!$F$1),MAX($A$5:$A8)+1,"")
A10=IF(OR(F10=Request!$F$1,G10=Request!$F$1,H10=Request!$F$1,I10=Request!$F$1),MAX($A$5:$A9)+1,"")
A11=IF(OR(F11=Request!$F$1,G11=Request!$F$1,H11=Request!$F$1,I11=Request!$F$1),MAX($A$5:$A10)+1,"")
A12=IF(OR(F12=Request!$F$1,G12=Request!$F$1,H12=Request!$F$1,I12=Request!$F$1),MAX($A$5:$A11)+1,"")
A13=IF(OR(F13=Request!$F$1,G13=Request!$F$1,H13=Request!$F$1,I13=Request!$F$1),MAX($A$5:$A12)+1,"")
A14=IF(OR(F14=Request!$F$1,G14=Request!$F$1,H14=Request!$F$1,I14=Request!$F$1),MAX($A$5:$A13)+1,"")
A15=IF(OR(F15=Request!$F$1,G15=Request!$F$1,H15=Request!$F$1,I15=Request!$F$1),MAX($A$5:$A14)+1,"")
A16=IF(OR(F16=Request!$F$1,G16=Request!$F$1,H16=Request!$F$1,I16=Request!$F$1),MAX($A$5:$A15)+1,"")
A17=IF(OR(F17=Request!$F$1,G17=Request!$F$1,H17=Request!$F$1,I17=Request!$F$1),MAX($A$5:$A16)+1,"")
A18=IF(OR(F18=Request!$F$1,G18=Request!$F$1,H18=Request!$F$1,I18=Request!$F$1),MAX($A$5:$A17)+1,"")
A19=IF(OR(F19=Request!$F$1,G19=Request!$F$1,H19=Request!$F$1,I19=Request!$F$1),MAX($A$5:$A18)+1,"")
A20=IF(OR(F20=Request!$F$1,G20=Request!$F$1,H20=Request!$F$1,I20=Request!$F$1),MAX($A$5:$A19)+1,"")
A21=IF(OR(F21=Request!$F$1,G21=Request!$F$1,H21=Request!$F$1,I21=Request!$F$1),MAX($A$5:$A20)+1,"")
A22=IF(OR(F22=Request!$F$1,G22=Request!$F$1,H22=Request!$F$1,I22=Request!$F$1),MAX($A$5:$A21)+1,"")
A23=IF(OR(F23=Request!$F$1,G23=Request!$F$1,H23=Request!$F$1,I23=Request!$F$1),MAX($A$5:$A22)+1,"")
A24=IF(OR(F24=Request!$F$1,G24=Request!$F$1,H24=Request!$F$1,I24=Request!$F$1),MAX($A$5:$A23)+1,"")
A25=IF(OR(F25=Request!$F$1,G25=Request!$F$1,H25=Request!$F$1,I25=Request!$F$1),MAX($A$5:$A24)+1,"")
A26=IF(OR(F26=Request!$F$1,G26=Request!$F$1,H26=Request!$F$1,I26=Request!$F$1),MAX($A$5:$A25)+1,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



ABCDEF
1Request 1
2
3
4HelperDrawingLine#Person
51Iso 54gasFrank
62Iso 54gasDave
73Iso 54waterFrank
84Iso 54nitrogenBill
95Iso 53waterBill
106Iso 54gasFrank
117Iso 52waterDave
12
13
14
15
16
17
18
19
20
21

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Request

Worksheet Formulas
CellFormula
A5=IF(MIN(Log!$A$5:$A26)=0," ",MIN(Log!$A$5:$A26))
B5=IF(ISNA(IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C5=IF(ISNA(IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D5=IF(ISNA(IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A5=" "," ",LOOKUP($A5,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A6=IF(A5>=(MAX(Log!$A$5:$A26))," ",MIN(Log!$A$5:$A26)+COUNT($A$5:$A5))
B6=IF(ISNA(IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C6=IF(ISNA(IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D6=IF(ISNA(IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A6=" "," ",LOOKUP($A6,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A7=IF(A6>=(MAX(Log!$A$5:$A27))," ",MIN(Log!$A$5:$A27)+COUNT($A$5:$A6))
B7=IF(ISNA(IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C7=IF(ISNA(IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D7=IF(ISNA(IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A7=" "," ",LOOKUP($A7,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A8=IF(A7>=(MAX(Log!$A$5:$A28))," ",MIN(Log!$A$5:$A28)+COUNT($A$5:$A7))
B8=IF(ISNA(IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C8=IF(ISNA(IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D8=IF(ISNA(IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A8=" "," ",LOOKUP($A8,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A9=IF(A8>=(MAX(Log!$A$5:$A29))," ",MIN(Log!$A$5:$A29)+COUNT($A$5:$A8))
B9=IF(ISNA(IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C9=IF(ISNA(IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D9=IF(ISNA(IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A9=" "," ",LOOKUP($A9,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A10=IF(A9>=(MAX(Log!$A$5:$A30))," ",MIN(Log!$A$5:$A30)+COUNT($A$5:$A9))
B10=IF(ISNA(IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C10=IF(ISNA(IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D10=IF(ISNA(IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A10=" "," ",LOOKUP($A10,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A11=IF(A10>=(MAX(Log!$A$5:$A31))," ",MIN(Log!$A$5:$A31)+COUNT($A$5:$A10))
B11=IF(ISNA(IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C11=IF(ISNA(IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D11=IF(ISNA(IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A11=" "," ",LOOKUP($A11,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A12=IF(A11>=(MAX(Log!$A$5:$A32))," ",MIN(Log!$A$5:$A32)+COUNT($A$5:$A11))
B12=IF(ISNA(IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C12=IF(ISNA(IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D12=IF(ISNA(IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A12=" "," ",LOOKUP($A12,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A13=IF(A12>=(MAX(Log!$A$5:$A33))," ",MIN(Log!$A$5:$A33)+COUNT($A$5:$A12))
B13=IF(ISNA(IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C13=IF(ISNA(IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D13=IF(ISNA(IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A13=" "," ",LOOKUP($A13,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A14=IF(A13>=(MAX(Log!$A$5:$A34))," ",MIN(Log!$A$5:$A34)+COUNT($A$5:$A13))
B14=IF(ISNA(IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C14=IF(ISNA(IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D14=IF(ISNA(IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A14=" "," ",LOOKUP($A14,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A15=IF(A14>=(MAX(Log!$A$5:$A35))," ",MIN(Log!$A$5:$A35)+COUNT($A$5:$A14))
B15=IF(ISNA(IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C15=IF(ISNA(IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D15=IF(ISNA(IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A15=" "," ",LOOKUP($A15,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A16=IF(A15>=(MAX(Log!$A$5:$A36))," ",MIN(Log!$A$5:$A36)+COUNT($A$5:$A15))
B16=IF(ISNA(IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C16=IF(ISNA(IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D16=IF(ISNA(IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A16=" "," ",LOOKUP($A16,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A17=IF(A16>=(MAX(Log!$A$5:$A37))," ",MIN(Log!$A$5:$A37)+COUNT($A$5:$A16))
B17=IF(ISNA(IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C17=IF(ISNA(IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D17=IF(ISNA(IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A17=" "," ",LOOKUP($A17,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A18=IF(A17>=(MAX(Log!$A$5:$A38))," ",MIN(Log!$A$5:$A38)+COUNT($A$5:$A17))
B18=IF(ISNA(IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C18=IF(ISNA(IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D18=IF(ISNA(IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A18=" "," ",LOOKUP($A18,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A19=IF(A18>=(MAX(Log!$A$5:$A39))," ",MIN(Log!$A$5:$A39)+COUNT($A$5:$A18))
B19=IF(ISNA(IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C19=IF(ISNA(IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D19=IF(ISNA(IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A19=" "," ",LOOKUP($A19,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A20=IF(A19>=(MAX(Log!$A$5:$A40))," ",MIN(Log!$A$5:$A40)+COUNT($A$5:$A19))
B20=IF(ISNA(IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C20=IF(ISNA(IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D20=IF(ISNA(IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A20=" "," ",LOOKUP($A20,Log!$A$5:$A$26,Log!$D$5:$D$26)))
A21=IF(A20>=(MAX(Log!$A$5:$A41))," ",MIN(Log!$A$5:$A41)+COUNT($A$5:$A20))
B21=IF(ISNA(IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$B$5:$B$26))),"",IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$B$5:$B$26)))
C21=IF(ISNA(IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$C$5:$C$26))),"",IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$C$5:$C$26)))
D21=IF(ISNA(IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$D$5:$D$26))),"",IF($A21=" "," ",LOOKUP($A21,Log!$A$5:$A$26,Log!$D$5:$D$26)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Request 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">Drawing</td><td style="text-align: center;;">Line#</td><td style="text-align: center;;">Person</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">Iso 54</td><td style="text-align: right;;">gas</td><td style="text-align: right;;">Frank</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">Iso 54</td><td style="text-align: right;;">gas</td><td style="text-align: right;;">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">Iso 54</td><td style="text-align: right;;">water</td><td style="text-align: right;;">Frank</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">Iso 54</td><td style="text-align: right;;">nitrogen</td><td style="text-align: right;;">Bill</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">Iso 53</td><td style="text-align: right;;">water</td><td style="text-align: right;;">Bill</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">Iso 54</td><td style="text-align: right;;">gas</td><td style="text-align: right;;">Frank</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">Iso 52</td><td style="text-align: right;;">water</td><td style="text-align: right;;">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Request</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Log!B$5:B$26,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">Log!$B$5:$B$26</font>)-ROW(<font color="Teal">Log!$B$5</font>)+1</font>)/(<font color="Purple">Log!$F$5:$I$25=$E$1</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Log!C$5:C$26,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">Log!$B$5:$B$26</font>)-ROW(<font color="Teal">Log!$B$5</font>)+1</font>)/(<font color="Purple">Log!$F$5:$I$25=$E$1</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Log!D$5:D$26,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">Log!$B$5:$B$26</font>)-ROW(<font color="Teal">Log!$B$5</font>)+1</font>)/(<font color="Purple">Log!$F$5:$I$25=$E$1</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
22
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thank you Fluff for the quick response! I didn't think when I made the sample sheet and had all the columns with the "Request 1" in the log next to each other. Is it possible to change this "(Log!$F$5:$I$25=$E$1)" to individual columns that will have populated columns between them? I tied to imbed an "OR" statement but it didn't work the way I did it.
I'll have to work through it to see exactly what's going on, I don't understand the "ROWS($A$1:$A1)" this point, I've never used the AGGREGATE formula before.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
As long as the other columns don't contain "Request 1" then simply change this
Log!$F$5:$I$25=$E$1
to reference the full range you want to look at like
Log!$F$5:$N$25=$E$1
 

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
22
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I will shuffle some columns around to make sure none of the ones between will need to have the request number in them. I appreciate the help, thank you again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,114,277
Messages
5,546,935
Members
410,764
Latest member
Dedeke
Top