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

[Silverhorne]

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.

	A	B	C	D	E	F	G	H	I
1	Data
2
3	Helper
4		Drawing	Line#	Person		6am	Noon	6pm	Midnight
5		Iso 54	water	Bill
6	1	Iso 54	gas	Frank				Request 1
7		Iso 52	nitrogen	Dave
8	2	Iso 54	gas	Dave					Request 1
9		Iso 54	nitrogen	Frank
10	3	Iso 54	water	Frank		Request 1
11		Iso 54	water	Frank
12		Iso 52	nitrogen	Frank
13		Iso 53	nitrogen	Dave
14	4	Iso 54	nitrogen	Bill			Request 1
15		Iso 53	nitrogen	Dave
16		Iso 53	water	Bill
17	5	Iso 53	water	Bill		Request 1
18		Iso 54	gas	Dave
19		Iso 52	gas	Bill
20		Iso 52	nitrogen	Bill
21	6	Iso 54	gas	Frank				Request 1
22		Iso 53	gas	Dave
23		Iso 53	water	Bill
24		Iso 54	nitrogen	Frank
25	7	Iso 52	water	Dave		Request 1
26		Iso 54	water	Bill

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

Log​

Worksheet Formulas Cell Formula 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>

	A	B	C	D	E	F
1						Request 1
2
3
4	Helper	Drawing	Line#	Person
5	1	Iso 54	gas	Frank
6	2	Iso 54	gas	Dave
7	3	Iso 54	water	Frank
8	4	Iso 54	nitrogen	Bill
9	5	Iso 53	water	Bill
10	6	Iso 54	gas	Frank
11	7	Iso 52	water	Dave
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 Cell Formula 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>

 

[Fluff]

How about

Book1
	A	B	C	D	E
1					Request 1
2
3
4	Drawing	Line#	Person
5	Iso 54	gas	Frank
6	Iso 54	gas	Dave
7	Iso 54	water	Frank
8	Iso 54	nitrogen	Bill
9	Iso 53	water	Bill
10	Iso 54	gas	Frank
11	Iso 52	water	Dave
Request

Cell Formulas
Range	Formula
A5	=IFERROR(INDEX(Log!B$5:B$26,AGGREGATE(15,6,(ROW(Log!$B$5:$B$26)-ROW(Log!$B$5)+1)/(Log!$F$5:$I$25=$E$1),ROWS($A$1:$A1))),"")
B5	=IFERROR(INDEX(Log!C$5:C$26,AGGREGATE(15,6,(ROW(Log!$B$5:$B$26)-ROW(Log!$B$5)+1)/(Log!$F$5:$I$25=$E$1),ROWS($A$1:$A1))),"")
C5	=IFERROR(INDEX(Log!D$5:D$26,AGGREGATE(15,6,(ROW(Log!$B$5:$B$26)-ROW(Log!$B$5)+1)/(Log!$F$5:$I$25=$E$1),ROWS($A$1:$A1))),"")

 

[Silverhorne]

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]

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]

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]

You're welcome & thanks for the feedback