VLOOKUP help (2003)

Topic

New Reply

I have a situation where I have a souce sheet that has different iterations of the same event number with different as of dates and different account numbers. I need a formula to return whether the event has completed. The results sheet will be updated daily with the completes and fails. The same event could fail every day for several days before completing. I need a vlookup (or something) that will check the event number and the as of date to return a status. Every time I try to use a vlookup or index/match, I keep getting stuck. Am I missing something basic here?

Reply | Quote

Replies

I don’t understand what the “As Of Date” has to do with the question you are asking. It looks like you could just check the status column for the event number you are interested. I can’t see any way to tell the status from the “As Of Date” column. Also, when is an event considered complete? When any status row shows “completed”?

Reply | Quote

The same event needs to be completed several times with different “as of” dates. So event 250634 for an as of date of 4/30, shows on the results with a “not processed” status. But the same event 250634 for an as of date of 5/31, shows on the results sheet with a “completed” status. I want the formula built into the yellow highlighted section of the source sheet. I put what the results should be just to identify them. So my logic (if it can be called that) is:

If the event is on the results sheet, if it matches the as of date, if it is complete, return a complete status.

Reply | Quote

OK, that makes more sense. However, I am still having problems.

1- On sheet Source cell F6, you show “Not Processed”, but the results sheet shows Completed for the same “Date as of”.

2- Cell F7 shows “future dated”, but the results show nothing for this date.

3- Will there be more than one event on each of the source and results sheets? If so, how will they be arranged?

Reply | Quote

You’re absolutely right on point #1. That was an error on my part. On point #2, just to make this more fun, if the “Date Next” is greater than the reporting date (today), the status will show “future dated.” That’s the easy part of the formula so I forgot about it. It’s the meat in the middle that I can’t get. See new additional examples in the attachment.

Reply | Quote

So does the account not matter, just the event and the date?

Does this do anything close to what you want? It assumes if it doesn’t find the event and the As Of date that it’s future dated. Also, it adds a column on each sheet to concatenate the event and the date for the vlookup.

Reply | Quote

YES YES YES!!!!

I think this is it. I couldn’t remember for the life of me what to do about the 2 different criteria. I totally forgot about concatenate. Thank you guys so much.

Reply | Quote