Vlookup & Hlookup..possibly... (in XL2003)

[pookgai]

Hi all! I'm fresh on these boards and know I can learn so much from you guys - hope you can help me solve this little problem that I have at the moment.

I understand the concept of lookups but when trying to combine them with other formulas, i'm failing miserably!

So, i have a table of project milestones dependencies from different projects all consolidated into one xls. My columns are:

A - name of project
B - the milestone unqie ID
C - unique id of the milestone it drives or it is driven by
D - whether the milestone is driven by or is driving another milestone
E - Task name
F - Finish Date
G - Says "Mismatch" or "OK" depending on whether the milestone dates match..this is the formula i need help with!!

Eg.
Project B | ABC-3 | DEF-1 | Out | Build Finished | 20-Mar-11 | Mismatch!
Project A | DEF-1 | ABC-3 | In | Build Finished | 01-Apr-11 | Mismatch!

Here, the milestone in proj B is the driving milestone for the milestone listed in project A, but the end dates don't match which is wrong. I want the automated forumla in column G to do some magical calc to highlight whether the date is correct or not.

It's not so important I get the mismatch calc on the "out" milestones (as that date won't be "wrong"), but its the "in" milestones that I need to highlight if it doesn't match its driving milestone date. Note, its not always a 1-1 relationship; there could be one milestone in one plan that lots of other projects are tracking.

So my thinking was to do the vlookup to match the unique code and somehow combine an if, and or hlookup to do a compare/match on dates .

Hope this makes sense, and hope someone can help!

 

[energman58]

If your data is in columns A2:F200 put the following in G2

=IF(INDEX(F2:F200,MATCH(C2,B1:B2200,0))<>F2,"MISMATCH!","OK")

And copy down

 

[pookgai]

Awesome !! That works a peach! thank you very much!!

I've now learnt two new functions as well!

 

[energman58]

You are welcome - I guess you spotted my typo in the "B" column part of the formula but for anyone browsing the threads here is what it should have said:

=IF(INDEX(F2:F200,MATCH(C2,B2:B200,0))<>F2,"MISMATCH!","OK")

 

[pookgai]

Yeah, I noticed the typo :P

I've even gone as far now to modify and include several IF statements in it now - yay! learned quite a bit from doing this . However, i want to introduce another layer..but i guess more for my own further education in the marvel of excel!

So, to summarise my table looks like:

Project B | ABC-3 | DEF-1 | Out | Build Finished | 20-Mar-11 | Mismatch!
Project A | DEF-1 | ABC-3 | In | Build Finished | 01-Apr-11 | Mismatch!

how do i introduce a new column which will work out the difference between the mismatch? so in eg above, it would work out the diff between 20th march and 1st April. I basically want a lookup or index/match function that will retrieve the matching values and return the difference.

Cheers

p.s are there any decent courses or books anyone can recommend where I can self teach myself more functions in a structured manner?

 

[energman58]

Not sure I understand what you are wanting to do here

Are you seeking to find the difference in days in a mismatch so in this case 11 days? What happens if it is not a mismatch - nothing?

Anyway if you can work out how the formula works you can easily modify it to get to the dates. If you take one date from the other excel will give you an answer in days (either positive and negative).

As to your question about learning excel there is a free PDF ebook on this site somewhere called "Learn Excel 2007" or something similar. While this is for 2007 it also has details of 2003 and is a pretty good resource and an easy read. But it isnt a "course" as such and it takes the "how do I do ..." approach rather than following through a structured course learning how to do a then b and then c this makes it good to dip into but may not suit what you are looking for.

The following link takes you to a good resource for a more structured general excel course with links to lots of video tutorials ranging from the extremely basic to the advanced:

https://people.highline.edu/mgirvin/ExcelIsFun.htm

HTH