Lookup same value but return multiple rows and columns

W

wlj777

New Member
Joined
Nov 2, 2017
Messages
5
Hi,
I have a very detailed workbook, so I have just taken a very small extract to help simplify my question.

You will see the simple sheet below
[TABLE="width: 698"]
<tbody>[TR]
[TD="class: xl69, width: 209, bgcolor: #D9D9D9"]PM TASK (CMS)
[/TD]
[TD="class: xl69, width: 151, bgcolor: #D9D9D9"]PM Frequency (weeks)
[/TD]
[TD="class: xl69, width: 77, bgcolor: #D9D9D9"]Trade
[/TD]
[TD="class: xl69, width: 88, bgcolor: #D9D9D9"]No of People
[/TD]
[TD="class: xl69, width: 118, bgcolor: #D9D9D9"]PM Duration (Hrs)
[/TD]
[TD="class: xl69, width: 132, bgcolor: #D9D9D9"]Task Requires Machine Shutdown
[/TD]
[TD="class: xl69, width: 154, bgcolor: #D9D9D9"]Downtime Hrs (HOURS)
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl67, bgcolor: transparent"]4
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl67, bgcolor: transparent"]12
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl67, bgcolor: transparent"]26
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl67, bgcolor: transparent"]52
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl67, bgcolor: transparent"]104
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]0.5
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl67, bgcolor: transparent"]26
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check
[/TD]
[TD="class: xl67, bgcolor: transparent"]104
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl67, bgcolor: transparent"]26
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Check seal flush centrifuge
[/TD]
[TD="class: xl67, bgcolor: transparent"]104
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[TD="class: xl68, bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl67, bgcolor: transparent"]26
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In a separate sheet, how could I write a formula to just lookup a particular 'Frequency Weeks' value (Eg 26) and return an example like below;


[TABLE="width: 394"]
<tbody>[TR]
[TD="class: xl67, width: 209, bgcolor: transparent, align: center"]26
[/TD]
[TD="class: xl65, width: 151, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 88, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 209, bgcolor: #D9D9D9"]PM TASK (CMS)
[/TD]
[TD="class: xl68, width: 151, bgcolor: #D9D9D9"]Trade
[/TD]
[TD="class: xl68, width: 77, bgcolor: #D9D9D9"]PM Duration (Hrs)
[/TD]
[TD="class: xl68, width: 88, bgcolor: #D9D9D9"]Task Requires Machine Shutdown
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Algiment check, integerty check
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Yes
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Visual inspection (6 monthly)
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mechanical
[/TD]
[TD="class: xl67, bgcolor: transparent"]0.3
[/TD]
[TD="class: xl67, bgcolor: transparent"]No
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi,

Let's say the source is sheet1 and target is sheet2 , you looking for value 26 .

In sheet2 : Ctrl+Shift+Enter NOT just Enter

A2 =IFERROR(INDEX(Sheet1!$A$1:$A$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
B2 =IFERROR(INDEX(Sheet1!$C$1:$C$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
C2 =IFERROR(INDEX(Sheet1!$E$1:$E$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
D2 = =IFERROR(INDEX(Sheet1!$F$1:$F$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
 
Upvote 0
I only want specific cells to be returned from each row that are associated with 26 in my example.
So if I selected 26 in the target sheet, I only want specific cells from each row to be generated.

admiral100 said:
Hi,

Let's say the source is sheet1 and target is sheet2 , you looking for value 26 .

In sheet2 : Ctrl+Shift+Enter NOT just Enter

A2 =IFERROR(INDEX(Sheet1!$A$1:$A$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
B2 =IFERROR(INDEX(Sheet1!$C$1:$C$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
C2 =IFERROR(INDEX(Sheet1!$E$1:$E$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
D2 = =IFERROR(INDEX(Sheet1!$F$1:$F$11,SMALL(IF(Sheet1!$B$1:$B$11=26,ROW(Sheet1!$A$1:$A$11),""),ROW(Sheet1!A1))),"")
Click to expand...
 
Upvote 0
That's exactly what it does ... according to your desired result :)[TABLE="width: 744"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]



[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PM TASK (CMS)[/TD]
[TD]Trade[/TD]
[TD]PM Duration (Hrs)[/TD]
[TD]Task Requires Machine Shutdown[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Algiment check, integerty check[/TD]
[TD]Mechanical[/TD]
[TD]2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Visual inspection (6 monthly)[/TD]
[TD]Mechanical[/TD]
[TD]0.3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Visual inspection (6 monthly)[/TD]
[TD]Mechanical[/TD]
[TD]0.3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Visual inspection (6 monthly)[/TD]
[TD]Mechanical[/TD]
[TD]0.3[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
419
XLfriend777
XLfriend777
1
  • Question Question
Sum total filtered data only, with multiple criteria
Replies
1
Views
458
1988craig
1
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
571
skyh3ck
skyh3ck
S
  • Solved
Match value from a specific cell with the same value in multiple columns, return the results of these rows from a specific column
Replies
5
Views
928
Fluff
Fluff
A
  • Question Question
COUNT two or more consecutive text values and check each if it's +/- than a value
Replies
2
Views
668
aspasovski86
A

Forum statistics

Threads
1,224,837
Messages
6,181,255
Members
453,028
Latest member
letswriteafairytale

Share this page

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