Excel lookup, multiple conditions

iits
iits used Ask the Experts™
on
Hi Experts

I have an excel spreadsheet with 2 sheets.

Sheet 1 is called "Report" and is what I use to paste a web based report from our management software into excel - the example shows sample data in the format it appears.  The number of employees could exceed 50, the products associated with the employee is NOT the same for each employee, if an employee sold zero of one product it will not show that row.  The value next to the product - Column C is the quantity sold.  Then there is sheet called Results, This table will show the column headers & list all the staff members in column A.  I need the values on this sheet to be 'looked up' from the 'report' sheet.  The totals do not need to be brought accross I will calculate these using SUM/SUMIF.  I also need it to be compatible with Excel 2003 & any N/A values to be shown as zero (0).

Thank you!
 Example.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Unfortunately, there is no easier way that I know of than to double up the formula in an IF.

=IF(ISNA(VLOOKUP(B$2,OFFSET(Report!$B$1:$C$5,MATCH($A3,Report!$A:$A,0)-1,0),2,FALSE)),0,VLOOKUP(B$2,OFFSET(Report!$B$1:$C$5,MATCH($A3,Report!$A:$A,0)-1,0),2,FALSE))

In B3. Copy to other cells.

Author

Commented:
Thanks Cyberkiwi! We're getting very close...
My only issue is (and correct me if I am wrong), the above code will not be quite accurate because it will always be indexing the chunk B1:C5 to look for data.  For example - refer to the attachment.  If 'Emily Thompson' only sells 1 type of product, and since this report isn't showing product types that equal zero, 'Peter Smith' will end up a lot closer to Emily meaning that the formula which actually pick the Pencil quantity from peter and put it under Emily on the report, when in fact the quantity should be zero for Emily.
Am I making Sense?
Thanks!

Example2.xls
Top Expert 2008
Commented:
Getting a bit long but it works:

=IF(ISNA(VLOOKUP(B$2,OFFSET(Report!$B$1:$C$1,MATCH($A3,Report!$A:$A,0)-1,0,MATCH("Total",OFFSET(Report!$A$1,MATCH($A3,Report!$A:$A,0)-1,0,100),0)-1),2,FALSE)),0,VLOOKUP(B$2,OFFSET(Report!$B$1:$C$1,MATCH($A3,Report!$A:$A,0)-1,0,MATCH("Total",OFFSET(Report!$A$1,MATCH($A3,Report!$A:$A,0)-1,0,100),0)-1),2,FALSE))

Place in B3 and copy to rest of table.

Kevin

Author

Commented:
Perfect! Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial