Lookup return values if meet criteria?

**lissamel** · 08-30-2015, 03:00 AM

Hi all,

I have probably named this poorly, but I have an excel need I can't figure out (or explain too well!). I have a spreadsheet that contains a list of products in the rows (Cell D to be specific) Other information about each product exists in the columns on either side. From Column L onward I have columns with People's names as headers, and the quantity of each product they ordered. The spreadsheet is long, and lots of people have 0 for a lot of things.

I would like to create two things-

1 - a copy of the data for each member but with only the rows that have a number greater than 0 in.
2 - a copy of the data but restricted just by the products that have at least one person to select, and include the order quantities for each person who ordered it.

Thanks in advance- this forum is great!

Mel

Very basic idea of what I have:

Product	Person name (qty)	Person (price calculated''0	Person name (qty)	Person (price calculated''0	Person name (qty)	Person (price calculated''0
Product A	7	$7.00	0	$0.00	2	2
Product B	8	$8.00	0	$0.00	12	$12.00
Product C	15	$15.00	0	$0.00	0	2$0.00
Product D	1	$1.00	0	$0.00	2	2
Product E	7	$7.00	0	$0.00	5	$5.00
Product F	6	$6.00	11	$11.00	0	$0.00

**etaf** · 08-30-2015, 05:38 AM

its possible a pivot table may help

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**lissamel** · 08-30-2015, 06:11 AM

Hi - thanks - I had thought a pivot table, but I can't get it to work. I will create a cutdown version of my sheet and upload it - it is very large!

Thanks

Mel

**lissamel** · 08-30-2015, 06:31 AM

Hi - mock up file attached. Thanks

**etaf** · 08-31-2015, 09:23 AM

Thanks for the mock up - very useful , however, with the data the way its laid out - nothing comes to mind quickly
hopefully another member may help - while i see if i can get a solution, but not simple for my knowledge

Shame its not laid out differently, then a pivot table would create the summary and possibly the order form

**Jacc** · 08-31-2015, 10:21 AM

Something like this? I used a helper column to make the formulas less messy.
The formula in the helper column is an array formula, it has to be entered with Ctrl + Shift + Enter.

Lookup return values if meet criteria?

LinkBack

Thread Tools

Rate This Thread

Display

Lookup return values if meet criteria?

Re: Lookup return values if meet criteria?

Re: Lookup return values if meet criteria?

Re: Lookup return values if meet criteria?

Re: Lookup return values if meet criteria?

Re: Lookup return values if meet criteria?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Left Lookup to Return Multiple Values Given 2 Criteria

[SOLVED] Lookup Values which meet two or more criteria and return multiple matches horizontally

[SOLVED] How to return all corresponding values that meet 2 criteria in excel

[SOLVED] Equivalent of an index match but return all values that meet the criteria

[SOLVED] Array Formula to Lookup and Return All Rows in Table that Meet Single Criteria

Return Single Row of Values That Meet Two Criteria

Multiple criteria lookup to return values in a column

Bookmarks

Bookmarks

Posting Permissions