Thank you for that confirmation. Here is the completed file:http://wikisend.com/download/459204/audit_example_2_jess1.xlsx
In that file you can see that I used the IFERROR function to clear the cells or to make the cells blank IN CASES where there are no matches. If there is no iferror function, then the cells where there are no matches found will be displaying #N/A.
In the actual file that you gave me, which I completed and submitted to you, all of the cells are blank. The reasons for this is because there were NO MATCHES found.
For instance, in row 2, the formula will lookup for the date 5/28/2013 in the second sheet. There are NO dates in the second sheet that is 5/28/2013, there is only one 5/27/2013. So in this case, all rows with dates 5/28/2013 will show blank.
The logic or flow of the formula that you described, is this:
- Excel will lookup the date in column T in the first sheet inside the second sheet.
- When a match is found, take column E of the second sheet as the result.
- With this result (data in column E in the second sheet), lookup for this rate in the third sheet and lookup IN the first column, A.
- When a match is found, display the percentage in the third column, C, as a result.
That was the logic I used to create the formula in the file I gave you above. However, in the looped vlookups, there were no matches found so all cells in column V (Invoice) are blank. I manually checked if there are matches, and there are really NO matches.
The only thing that will produce matches is when we use Column B in the third sheet as the lookup column and we will temporarily change the date in the second sheet from 5/27/2013 to 5/28/2013 in cell B22 to produce a match to test the logic in the formula.
Here is the completed file:
In either formula used in the 2 files I gave you, as long as there are matches in the actual data that you have there, the percentages will be shown. If not, then the cells will be blank because of the iferror function.
Please remember to rate my service positively (3-5 stars/faces) if this helped. This is the only way that I can get credit for my work. Tips are always highly appreciated!
If you have any other questions, please ask me or reply to me – I’ll be happy to respond and help you further.