Combining Excel Sumif, Vlookup, If Is Error Functions, etc

Excel function tutorials

Hi everyone, today I received an interesting question by email which I wanted to share since it was not the first time I had received this type of question. In this specific case, it required me to use several of the functions that I have been writing about on this blog so I thought it would make an interesting case. First, let me explain what the user asked for. You will not have the exact same problem but I think that trying to understand it can help you a great deal. Basically, this user has a list of documents that you can see in column A. Then, there is a reference for that document and a date in columns B and C. What this reader would like is:

-For each document number, get the reference number for the most recent modification

The tricky part here is that most documents appear several times (becaused they were modified on several dates)

Alternatives

Problems like this are so interesting because there so many different ways to get them done. One way would have been to do a macro with a loop as I did a few weeks ago. I will however do it without a macro for this time, it should be as simple.

My first step is to simply sort the entries by date, this is the basis of everything that I will do, so here is a simple sort:

Then, I simply want to see for each line, is there a more recent entry for that specific number. In other words, is there a row, above the current one, that has this number. How would I do it? This concern does not exist for the first row, so cell D2, which will determine if such an entry exists will be simple:

“Y” so this means that “Y” it is the most recent.

For the next line, I would check by doing a vlookup. Vlookups are actually a great way of simply validating if a specific data exists. Here is how I will do it. First, here is my initial formula for D3:

=VLOOKUP(A3,$A$1:A2,2,FALSE)

I currently get an error since it doesn’t.

But really, what I need, is simply to know if it exists. It is difficult to work with errors. So I use if “if is error” fundction” and will replace this with:

=IF(ISERROR(VLOOKUP(A3,$B$1:A2,2,FALSE)),””,”Y”)

Basically, if there is an error (not found), I will enter “Y”. However if there is no error, it means that there is a more recent entry so I will leave it blank. I can then use this formula for the entire set, you can see the result here:

Then, you can simply add a simple “if” such as:

=IF(D2=”Y”,A2,””)
=IF(D2=”Y”,B2,””)

The end result is:

You can download the spreadsheet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

One Feedback on "Combining Excel Sumif, Vlookup, If Is Error Functions, etc"

Rajeev

What is use of this formula ” IF(D2=”Y”,A2,””)” as you given example,please make me application use in excel?