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