Blogger
Share this content
0
6
18658

Excel: Using Match & Index to replace Vlookup

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

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.

Thanks (0)

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)

Thanks (0)

Mmm...
Six and a half years is a long time to answer a question. I think the problem may be solved by now.

Thanks (0)

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!

Thanks (0)

Thanks for the explanation.

Thanks (0)

Just my hobby horse!
This is probably me moaning about VLOOKUP, which (if incorrectly used) can give results that look OK but are wrong, and sometimes requires doing some initial re-arrangement of the lookup data, which may or may not be convenient.

You are actually looking up the value of B21 in column H, but if you insert or delete a new column between D and J your formulae will no longer give the right answer. And you are giving Excel a much larger array than it needs, which could have performance implications if there were a very large amount of data.

Using INDEX and MATCH the formulae will update automatically if you add extra columns etc.

=INDEX($H$7:$H$16,MATCH(B21,$D$7:$D$16,0))

in other words

INDEX(Column to look up,MATCH(Cell to match, Column with desired result,0)

However, you are using VLOOKUP correctly (I assume) and with a small amount of data so it makes no odds so long as you have no need to change the lookup data.

And I agree that Excel Help is less than helpful on this topic.

Thanks (0)