Return entire range if value exists in cell

[dwest870]

Hi, first post here. I searched but couldn't find what I was looking for, so here it goes.

I'm looking for a formula to search a specific cell for a value, and if that value exists, return the entire row. For example, in the attached screenshot, the top table is the raw data. If a 1 exists in cell B3, I want to pull the entire range (B3:Q3) from the row into a separate table.

The bottom table is the result I'm looking for. Does this make sense?

Table.png

Thanks in advance!

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dwest870]

Sample worksheet attached

[Pete_UK]

Put this formula in S3:

=IF(B3=1,MAX(S$2:S2)+1,"-")

then copy down to S14.

Then in cell A18 you can use this formula:

=IF(ROWS($1:1)>MAX($S:$S),"",INDEX(A:A,MATCH(ROWS($1:1),$S:$S,0)))

Copy this across and down as required.

Hope this helps.

Pete

[dwest870]

PETE!!! Thank You Thank You Thank You!!!!! This worked perfectly! I appreciate your help!!

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete