Thread: Single Value to bring up multiple rows (but want to use multiple values)

Hello everyone,

This is my current situation: I have an excel spreadsheet for my work. On Sheet 2 (labeled DATA) there is every student in my school along with info for each term they were registered (called reporting terms). So ID#'s are listed multiple times in column A in DATA. ex: ID#12345 for Fall2010 reporting term (student enrolled) and ID#12345 for Spring 2011 (student enrolled)

I would use this formula to look up multiple ID#'s and match them with their corresponding counselor. Since counselor does not change across the semesters, it didn't matter if vlookup just brought up the first value.
=VLOOKUP($A$2:$A$8729,DATA!$A$2:$E$65536,5,FALSE)

I created this formula to return multiple values for 1 iD# in cell A2 to show me all reporting terms for that ID#: (ctrl+shift+enter)
=if(iserror(index(data!$A$2:$AB$65536,SMALL(IF(DATA!$a$2:$A$65536=$A$2,rOW(DATA!$A$2:$A$65536)),rOW( 1:1)),8)),"",index(data!$A$2:$AB$65536,SMALL(IF(DATA!$a$2:$A$56000=$A$2,rOW(DATA!$A$2:$A$65536)),rOW (1:1)),8))

This formula works great, but i have to pull down the formula to bring up all the reporting terms for the ID# in A2

I want to be able to copy and paste a list ID#'s in column A and have it return the all the reporting terms for each ID# in column B.. is this possible?

ex: put ID#12345 in A2 and ID#23456 in A3, run formula and get something like this:
A_______ |__B
1| ID#12345 | Fall2010 reporting
2| ID#12345 | Spring 2011 reporting
3| ID#23456 | Fall2009 reporting
4| ID#23456 | Spring 2010 reporting

Any help would be greatly appreciated!

I have also posted this topic on MrExcel.com see http://www.mrexcel.com/forum/showthr...84#post2639584

Why not use a Pivot Table ? A Pivot would on first impression appear to be a far more efficient approach.

Can you also confirm which version you're running this on ?
Your profile states XL2010 but your sample formulae would indicate some backwards compatibility requirements.

@Burgers, please note:

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web (eg MrExcel).

You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.

We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this

Originally Posted by Burgers

What backwards compatibility issues are you seeing?

Not *issues* per se simply that in XL2007+ we'd use IFERROR rather than double Evaluate (esp. when working with resource intensive formulae as is the case here)
(also references to 65536 are more relevant to versions prior to XL2007 which has >1m rows)

Originally Posted by Burgers

How would you recommend setting up the pivot table?

We don't know how your source data is laid out exactly on the Data sheet - we can I think presume ID# to be Col A and Counsellor Col E ... I suspect reporting terms are in Col H ?

Assuming the above to be correct and given XL2010 in terms of the Pivot:

-- set all three fields as Report Labels (ID, Counsellor & Reporting Term - in that order)

-- opt for a Tabular Report Format (via Design Tab on Ribbon when PT active)

-- disable Subtotals & Grand Total (via Design Tab on Ribbon when PT active)

-- right click on an ID# header -> Field Settings -> Layout & Print -> Repeat Item Labels (& repeat for Counsellor field)

You can then filter the set by selecting whichever ID#'s are of interest.

Ok, I had some time today, and the pivot table will def do what i need it to do, thanks DonkeyOte.

Only issue i am running into is getting it to single out the ID#'s i need. I have a list of over 1000 ID#'s to look up. Going through manually and selecting each individual ID# in the filter will take far to long.

Is there a way I can use my pivot table data, and then add my list of 1000+ ID#'s to sort by?

Thanks

EDIT: I converted my table to a range --> inserted my Smaller ID# list as column A --> then I used this formula =ISNUMBER(MATCH(B1, A:A, 0)) to match my smaller ID# list to my larger ID# list, and sorted by TRUE --> copy and pasted into a new excel sheet --> delete all the FALSE and the larger ID# list -->converted to table --> Pivot table

Everything looks good, I spot checked and everything seems to line up correctly. Thank you for all your help DonkeyOte. Can mark forum as SOLVED