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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
400
XLfriend777
XLfriend777
1
  • Question Question
Sum total filtered data only, with multiple criteria
Replies
1
Views
451
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
536
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
923
Fluff
Fluff
A
  • Question Question
COUNT two or more consecutive text values and check each if it's +/- than a value
Replies
2
Views
659
aspasovski86
A

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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