Forum Discussion
HOw to use, IF, AND, Lookup, match, maybe all or none of these!
Good evening/morning,
I could do with some help on the example workbook I have added. What I am trying to acheive is this:-
Include all of the relevant invoice numbers from sheet 2, onto sheet one if the employee number and period number matches on both worksheets. I want this to work and include all of the hours for the 2 tests on sheet 2, matching the total hours worked on sheet 1. If the total hours on sheet 1 does not match the breakdown on sheet 2 (providing the emp number and period number also match), then "investigate".
So, for example, employee 1001 on period 1 should have invoice numbers 2001 & 2007 returned.
My brain is too fried to work this out tonight.
Many thanks in advance
14 Replies
- Thanks Detlef Lewin, any chance of a video to show me how to set this up?
There is this thing called web search.
https://www.google.com/search?q=add+data+model+pivot+table
https://www.google.com/search?q=add+text+to+values+area+of+a+pivot+table
=IF(SUMIFS(Sheet2!$C$2:$C$10,Sheet2!$A$2:$A$10,Sheet1!$A2,Sheet2!$B$2:$B$10,Sheet1!$B2)<>Sheet1!$C2,"Investigate",IFERROR(INDEX(Sheet2!$D$2:$D$10,SMALL(IF((Sheet2!$A$2:$A$10=Sheet1!$A2)*(Sheet2!$B$2:$B$10=Sheet1!$B2),ROW(Sheet2!$D$1:$D$9)),COLUMN(Sheet1!A1))),""))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell D2 and filled across range D2:H5.
- OliverScheurich
This now works, thank you. There was problems in the real data, and also with formatting.
I have a further issues, I have noticed a lot of 'investtigation' needed. This is because there are employees who have had multiple entries, e.g. on sheet 1 same employee number with more hours.
I have added this on new rows on both worksheets and highlighted yellow. I would be most grateful if you could add some formula to be able to rectify this problem.
Many thanks in advance- Re:there are employees who have had multiple entries, e.g. on sheet 1 same employee number with more hours.
I guess the question will be intuitively clear if you share a sample file.
- Thank you OliverScheurich, this works on the sample workbook fine, but when I apply it to the real workbook it doesn't work. I think it may be that there is some data missing from some cells which breaks the formulas perhaps? I am going to see if the other answer given can create the pivot table and DAX, as that is alien to me. I will keep trying with your answer too, I think i have the formula all correct, so will keep plugging at it for now.
