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
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
