Originally Posted by kccrow
Two considerations on VLOOKUP vs INDEX(MATCH...
VLOOKUP requires alot of processing power because it evaluates an entire table at once, which is why some workbooks with large data sets seem to take forever to populate a change. VLOOKUP cannot look left of the lookup value. Adding and deleting columns in VLOOKUP also, often, breaks formulas because of the static nature of calling the number of columns to the right to look for. You have to change the formula from VLOOKUP to HLOOKUP to handle vertical and horizontal matching.
INDEX and MATCH have a bit more flexibility and require significantly less processing power because it evaluates a cell reference rather than an entire table. INDEX/MATCH can look anywhere on a sheet: left, right, up, and down. INDEX/MATCH rarely uses static values unless you explicitly tell it to. Most often there is no need to have a static reference value, which means you can add and delete columns and rows with no effect on the outcome, providing you set up the formulas appropriately.
Basically, VLOOKUP is a nice beginner's tool but if you want to do more and get more out of Excel, then learning to use other functions like INDEX, MATCH, LEFT, LEN, ADDRESS, INDIRECT and so forth are going to make your life MUCH easier and more rewarding. Those functions used in conjunction with one another make Excel versatile and allows you to use it like a database. Most beginners struggle with logic though, so keeping the formulas straight and knowing what they are doing is extremely important.
Yes, you could just as easily set up an SQL database with two tables for the names and use an INNER JOIN function to return a list of matches or a LEFT/RIGHT JOIN to return one of the lists entirely and all the matches in the other list. It wouldn't be terrible at all to set up, especially if you can code an HTML UI parsing an INSERT INTO function, but most people that are working with Excel don't also use or know SQL. Even then you're going to have to end up with adding some more complex statements using wildcards and such given the parameters Coach outlined because the data sets don't exactly match. Anyhow, pick one, learn one, and then move on from there. For the vast majority of people, learning Excel will be much more valuable in day-to-day business.
|