VLOOKUP return multiple values

[crttrlvr]

Hi,

I've seen several solutions to similar problems to mine, but have been unable to adapt them to suit my needs.

I have attached a very basic dummy workbook similar to the one I'm working with.

I need a summary sheet which will autofill the margins from each sheet upon entering the Sales Order # in column A on the Summary sheet. I manually entered the information in my dummy workbook. I will have a Summary sheet for each month. The challenge for me is the Sales Order # may appear more than once on a single sheet. Another challenge is if there are multiple margins for a particular Sales Order # for the same job type, those would need to be listed seperately so they can be averaged in with all margins for that Sales Order, or alternatively, they could be averaged and only the average would be entered into the corresponding cell.

Thank you...I hope my question makes sense....

Jill

Jill working margins.xls

[dilipandey]

Hi Jill,

How do you get 30 in Cell D2.. I have checked and it is not appearing in any of the sheets against:-

10001 Mark 1234 Or,

1001 or,
Mark or,
1234

Further to this I am not able to understand if it is average.. if so how?

Are you considering only the Sales order# to fill up the sheet as that too is not fitting in for calculation purpose ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[estige]

Hi, you can get for example Mark's average (30) from sheet April Dicing by enering the following code in D3. You must use ctrl/shift/enter to enter the code.

Please Login or Register  to view this content.

[crttrlvr]

DILIPandey,

The '30' in cell D2 is the average dicing margin for sales order 10001. If you look on the April dicing sheet, there is a margin of 50 and a margin of 10. The average of 50 and 10 is 30.

Yes, the search criteria used in this worksheet would be the sales order #. Excel should search all sheets for a particular month for the sales order # and return the appropriate info into the appropriate cell on the summary sheet. For April, there are two dicing margins, one packaging and one assembly which are all manually entered on the summary sheet I provided to show what I would like Excel to autofill for me.

Jill

[crttrlvr]

Hi, you can get for example Mark's average (30) from sheet April Dicing by enering the following code in D3. You must use ctrl/shift/enter to enter the code.

Please Login or Register  to view this content.

estige,

Thank you...the problem is, that formula returns the average for all dicing margins for April rather than only those that belong to the sales order entered on the summary sheet. I need Excel to only consider the rows that correspond to a particular sales order, then return the average margin for those rows.

Jill

[ron2k_1]

If you have office 2007 D2=:

Please Login or Register  to view this content.

If you have office 2003 and earlier D2:

Please Login or Register  to view this content.

This is for April dicing column, you will have to change the word "Dicing" for "Packaging" for the "Packaging" column. And you will have to replace "April" to "March" for the March Summary and so forth and so forth.

I'll give you something using DIRECT in a while, but this will get you started.

[crttrlvr]

If you have office 2007 D2=:

Please Login or Register  to view this content.

If you have office 2003 and earlier D2:

Please Login or Register  to view this content.

This is for April dicing column, you will have to change the word "Dicing" for "Packaging" for the "Packaging" column. And you will have to replace "April" to "March" for the March Summary and so forth and so forth.

I'll give you something using DIRECT in a while, but this will get you started.

Oh, thank you!!! I'm excited to try it out....have a big executive from our company spending time at our office the next couple days so may not be able to fiddle around with this for a bit. I'll let you know how it works for me once I've tried it.

I have Office 2003, btw, but others will be using my finished product on 2003 and 2007.

Jill

[crttrlvr]

This worked perfectly....thank you very much. My next challenge is to create a lookup sheet in which search criteria can be entered in a cell which will return only the information pertaining to the search criteria. I would like to have the option of searching by sales order, quote, salesperson or part #. The function would search all summary sheets for the year, so there would eventually be 12 sheets to search.

Thanks again for your help!!!

Jill