I have used both these functions extensivley, but I cannnot see a benefit of using one over the other. Does anyone know the relative strengths and weaknesses of these two lookup options?
Howdy. With VLOOKUP the column on the left has to be sorted for it to work properly. For INDEX/MATCH, there is no need to sort, and you can use any column to do the lookup. INDEX/MATCH is much more flexible.
Rich:
I thought VLOOKUP needed a sorted lookup column as well, though I have seen several examples where it as worked without being sorted. Good point on being able to use any column with the INDEX / MATCH.
VLOOKUP does not need the data to be sorted if you use false as the last parameter. It has the limitation that only colums to the left of the referred item can be 'looked up'
Index and match is more flexible but IMO takes a bit more learning.
Shades, nice to see you are still around and helping out!
Howdy, Ed. As noted on another thread, with my new position (since April) I don't get here often, but I occasionally miss the work in Excel. Then I sit down, and it passes...