can VLOOKUP return multiple rows in dynamic array?

Windyisle

New Member
Joined
Feb 16, 2011
Messages
9
Greetings,

I've got my massive project almost up and running (thanks in large part to the awesome help I've received here).

One more hurdle to clear:

I've got a master report, from which I'm looking up multiple lines from the database and dropping them into a finalized sheet. The records that I'm working with are 90% of the time in one row (ie #1543 data data data data). But I've got about 200 results that need to be displayed in multiple rows like this:

#1452 data data data data
.........data data data data
.........data data data data

I've tried entering this information into several rows with the same header:

#1452 data data data data
#1452 data data data data
#1452 data data data data

but VLOOKUP only returns the first row with that value.
Is there any way I can do something like:

#1452a data data data data
#1452b data data data data
#1452c data data data data

But have VLOOKUP still searching for #1452?

The complication is that its already in the middle of a bunch of calculations and data movement that is all automated at this point. I have formulas that pull out the #1452 from the report, then look up the record, then paste it in a 'calculation sheet' which corresponds to each row in the report (but only returns values that meet the conditions I've set up) - after which it gathers all the relevant entries from the calculation sheet and pastes them in a dynamic array in the final report.

The only other option is going into the database and entering the data in the SAME ROW using Alt+Enter to create soft carriage returns. This works, and would solve my problem, but I just want to make sure I'm not missing an easier (and cleaner) solution before I go ahead and start cramming multiple lines of data into the same cell (which makes me sad).

Thanks
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,187
Members
409,749
Latest member
BorisYeltsin

This Week's Hot Topics

Top