I have seen comments that the match & index functions in Excel are more reliable than vlookup. Why is this? I would appreciate some guidance in using these functions in a practical example. I looked in the Help function and ended up baffled!
I use vlookup to identify cost centres for charging vehicle costs. The data about cost centres is in D7:J16 and the vehicle codes are in,say, B21:B30. So my formula in c21 =VLOOKUP(B21,$D$7:$J$16,5,FALSE). How would I use match and Index to replace this formula?
Thanks for your help.
Paul
Paul Olson
Replies (6)
Please login or register to join the discussion.
Clarification
I guess the comment was less for Paul and more for anyone who stumbled across this thread.
You might like to look at this more in depth article on the subj
Matching data in a table in Excel using INDEX and MATCH (- a VLOOKUP alternative)
Mmm...
Six and a half years is a long time to answer a question. I think the problem may be solved by now.
Dyed in the wool
I'm a dyed in the wool vlookup man, but I promised to use index and match in a different thread, next time I needed a look up. So I have!
The data that I needed to match was in a different format in the two tables (text and number), so vlookup failed. However, index and match worked just fine.
Now I just have to reprogram my brain!