Goodbye Vlookup: Index/Match can play nice with sorting
I’ve been using a lot more of Index & Match functions together instead of Vlookup because it’s a faster calculation for Excel, faster for me to put in and copy across (especially multiple columns of lookups), and can go pull back values both right & left of the lookup column (as well as a matrix lookup).
I ran into a sang recently, though. By default, Index/Match doesn’t play nicely with sorts. It almost hard-codes the formula into the cell so your values get completely mixed up after a sort and is now looking up the original value that is likely on a different row.
Google found me the solution. Simply remove the sheet reference (bolded) of the lookup value right after the match: =index(sheet2!B:B,match(sheet1!A2, sheet2!A:A, 0). It will put all sheet references in by default, so type the formula, hit enter, and then correct it…and then copy it down.
