Find certain number then copy it and 4 other cells to other worksheet

[empty12]

Hi, I am having trouble creating a formula. I am trying to search 2 separate worksheets for a certain number (ex. 34) and then copy that cell and the 4 other cells directly to the right of it. I want to them past them to a separate worksheet. Any thoughts on how I can do this? Thanks

[JBeaucaire]

I would use a two-step INDEX(MATCH) or VLOOKUP(). With no sample sheet to offer specifics to you, then the construct would look like this:

On sheet3

=VLOOKUP(A1, Sheet1!$A:$E, COLUMNS($A$1:B$1), 0)

After entering that formula, copy that cell to the right for the other values.

This checks one sheet. Since you have xl2007, you can use IFERROR() to provide instructions if an error occurs on the first sheet, for instance the value is missing.

=IFERROR(VLOOKUP(A1, Sheet1!$A:$E, COLUMNS($A$1:B$1), 0), VLOOKUP(A1, Sheet2!$A:$E, COLUMNS($A$1:B$1), 0))

This checks two sheets.

If there is a possibility the value doesn't appear on EITHER sheet, then you can add one more IFERROR() to handle that:

=IFERROR(VLOOKUP(A1, Sheet1!$A:$E, COLUMNS($A$1:B$1), 0), IFERROR(VLOOKUP(A1, Sheet2!$A:$E, COLUMNS($A$1:B$1), 0), ""))

[empty12]

Thanks! this works for the first group of five columns but I have four of them. For example,
A1 has the one I am looking for, and B1:E1 has what I need Copied. But then I also need it to look at F1, K1, and P1 to Check those columns on both sheets. Is that possible? Let me know if this makes sense.

[JBeaucaire]

Show me. Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[empty12]

I hope that works. Both Sheets are the same size and setup, and they have 4 groups of five columns. Basically double the example I posted.

[empty12]

And if it changes anything, the numbers on the original two sheets are not in any order, they can be in any of the 4 columns I want to look in. On the third sheet though they will go down a column in order

EDIT: I think I have figured it out. Let me know if there are any pitfalls in the formula or any way to simplify it. Thank you for your help. I attached the sheet.

[JBeaucaire]

Can you save your workbook in .xls format? I see nothing but errors on sheet3 in the sample.

Your data layout is not standard, data should be one row per, no staggered as you have it. Fixing that to a standard format makes the formulas simple.

[empty12]

I see what you mean. When I saved in xls it said too many nested formulas. The way the sheet has to be set up makes it look like the example except there are about 40 rows of data. This is the formula I have:

=IFERROR(VLOOKUP($A13,Sheet1!$A:$E,COLUMNS($A$1:C$1),0),IFERROR(VLOOKUP($A13,Sheet1!$F:$J,COLUMNS($F$1:H$1),0),IFERROR(VLOOKUP($A13,Sheet1!$K:$O,COLUMNS($L$1:N$1),0),IFERROR(VLOOKUP($A13,Sheet1!$P:$T,COLUMNS($Q$1:S$1),0),IFERROR(VLOOKUP($A13,Sheet2!$A:$E,COLUMNS($A$1:C$1),0),IFERROR(VLOOKUP($A13,Sheet2!$F:$J,COLUMNS($F$1:H$1),0),IFERROR(VLOOKUP($A13,Sheet2!$K:$O,COLUMNS($L$1:N$1),0),IFERROR(VLOOKUP($A13,Sheet2!$P:$T,COLUMNS($Q$1:S$1),0),0))))))))

I basically just repeated the original formula for each column group

[JBeaucaire]

Ugh. Well, if you can put that back into one data set per row, that formula regains some sanity. But if this works for you, there you go!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[empty12]

Thank you very much! That was a huge help