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 shee...
=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.peiyezhu
You make a very valid point. Please now see attached updated sample workbook.
Many thanks in advance
- 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.
