Lookup & skipping blank rows

Hello, first time posting and I think I am in the right spot.

I am currently working on my Vendor Buying Guide Workbook. We have over 250 items we order weekly and 4 vendors we order from. I am working on creating Sheets for each vendor that have the items we buy from them, which changes frequently as our vendors' prices change often. I have the lookup working fine, with the unit and the pricing.

The problem is, I cannot figure out a formula to get rid of blank lines. I hope I attach my example Excel sheet correctly. If not, please alert me and on how to do so. Please be sure to look at all three sheets, Items, Vendor1 and Vendor2.

Thanks again for the help! My boss needs this by the end of the week and I have been working on it all day. I have worked with Excel for two years but it has been pretty basic stuff. This is the first time I've run into a problem that forums and google couldn't help me with.

The following formula is the basis for getting the values from 'Items' without rows of empty cells between those with non-empty cells:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Excel is having a hard time trying to figure out what I'm doing. I enter the formula exactly (changing the sheets and cell numbers to what I need) and afterwards prompted with the error message, informing me the formula is wrong.

Would someone mind breaking down this formula for me?

EDIT: Damn commas and parenthesis...

=IFERROR(INDEX(Items!B$3:B$10,AGGREGATE(15,6,(ROW(C$1:C$7))/(Items!$D$3:$D$10=$A$1),ROW(A1))),"") My best guess without seeing a file is that you changed the Arguments inside the first ROW function to match your rows. Those arguments report the number of rows to be indexed. If say you have 500 vendors the argument could be ROW(A$1:A$500)
To break down the formula from the inside out:
Have already talked about the first ROW function.
The denominator looks to see if the vendor in column D of the item sheet matches the name in cell A1, this produces a true/false array.
When the row numbers in the numerator are divided by the true/false array an array of numbers (rows) and #DIV0 errors is created. You can see this by running Evaluate Formula from the Formula tab.
AGGREGATE's second argument, 6, will ignore the #DIV errors and the first argument, 15, will look for the small(est) number (row) based on the ROW function at the end of the formula, so that when you look at the formula in say Vendor1!B5 you see that the last ROW function has the argument A3, meaning it will find the third smallest (row) number.
The INDEX function then looks at the value passed to it by AGGREGATE to find the item, unit or price that is the the nth row. So when aggregate passes a 1 it looks to the first row of the B3:B10 which is B3.
In the event that there isn't one, say when Vendor1!B6 looks for the 4th smallest number, IFERROR returns a blank instead of an error.
I would suggest that you may want to upload another file that is as close as possible in layout to your actual spreadsheet, including sheet names, while still protecting private/proprietary information.
You are using version 2013, correct?
Let me know if you have any questions.

=IFERROR(INDEX(Items!B$3:B$10,AGGREGATE(15,6,(ROW(C$1:C$7))/(Items!$D$3:$D$10=$A$1),ROW(A1))),"")

Try this array formula**:

=IFERROR(INDEX(Items!B:B,SMALL(IF(Items!$D$3:$D$10=$A$1,ROW(Items!D$3:D$10)),ROWS(A$1:A1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.