Lookup columns with several same names

[pingpong1]

I have a column of some 100 names which are often duplicated with 12 cells to the right containing numbers on one sheet.

In another sheet I want to type one of those names and have Excel look up that person from the sheet where he is mentioned several times and have those rows 12 cells replicated one after the other starting on the same row as the name and the rest underneath. Thanks for any help, Paul P.

[FlameRetired]

In the attached 'Source' sheet is as you describe.

In the 'Summary' sheet a drop down for Name criteria in A1.

Then array-enter this formula in A2 fill down until you get blanks and across as far as needed.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[pingpong1]

The Summary Sheet formulas are already filled out, the first 5 rows have #NAME? and the rest #VALUE! I copied that formula in and the last part of the formula goes like this: ROWS(!#REF!$2:2))),"") I get Error 508. I deleted !#REF! but still got Error 508. Thank you for your time PaulP

[FlameRetired]

The Summary Sheet formulas are already filled out, the first 5 rows have #NAME? and the rest #VALUE! I copied that formula in and the last part of the formula goes like this: ROWS(!#REF!$2:2))),"") I get Error 508. I deleted !#REF! but still got Error 508. Thank you for your time PaulP

PaulP,

What version of Excel are you using? IFERROR will return a #NAME? error in versions older than Excel 2007.

The #VALUE! errors likely show up because array formulas need to be "committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter" and not just Enter.

When array formulas are properly committed there will be curly braces {} around the formula in the formula bar. You don't type these in yourself. Excel does is for you. If you do try to type them in you will get an error.

I just uploaded my post #4 file and it showed no ROWS(!#REF!$2:2) errors. There is nothing in that part of my formula that is capable of producing #REF! errors.

If you upload your workbook ... saved with errors active ... we can have a look at it. Please be sure to remove sensitive data.

In the meantime please take the time to update your profile. Many members write their formulas taking into account profiled release versions.

[pingpong1]

It opens in Apache Open Office automatically! I am using MS Student and Teacher. Those curly braces are in your formula when I have pasted it but disappear when I click in the formula bar to remove them. Ill see how to alter my profile. It will ony stay in an ODS file !!!

[FlameRetired]

Those curly braces are in your formula when I have pasted it but disappear when I click in the formula bar to remove them.

That's not how to do it.

1. Copy the formula an paste as usual.
2. Then go into edit mode (click in the formula bar or hit the F2 function key).
3. While simultaneously holding down Ctrl and Shift hit Enter.
4. That is what makes the curly braces appear.
5. They are confirmation that the formula has been properly committed.

If you are doing this in Open Office I don't think I can be of much help. It's been awhile since I've used it. There may be different functions that do the same things as Excel. Their names escape me though.

Try starting Excel first and open from there.

[pingpong1]

Ok, so I did that I have {} at the beginning and end of the formula and each cell has #NAME?. Forget it I just saw you said I would get that with pre 2007 & my T&S is 2003 so I guess its not possible? PaulP

[popipipo]

IFERROR doesn't work with excel 2003
Try it this way without it.

[pingpong1]

Willem that works perfectly. Your English is great My Excel is very poor Problem Solved with gratitude.

[FlameRetired]

Willem,

How did you suppress the errors?

Edit Never mind. I figured it out. Very clever.

[FlameRetired]

Paul P,

If you would rather not use conditional formatting to suppress the errors the only other approach pre-2007 that I know of requires a longer array-entered formula. Try this:

Please Login or Register  to view this content.