VLookup vs. Match/Index

W

Wilma

Can someone please explain the difference between Vlookup and Match/Index.
The formula I am using is a vlookup (see below)--but I am wondering if using
another formula (match/index?) would be better.

The formula works but if a column is inserted it would skew the result. Any
advice?

=IF(ISERROR(VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE))
 
B

Bob Phillips

MATCH INDEX can look left as well as looking right, but is not better, in
fact is two function calls rather than 1.
 
T

T. Valko

The main difference between VLOOKUP and INDEX is that with INDEX you can
define both the row where to find your lookup_value and the column from
which to get the result. With VLOOKUP, the row where to find the
lookup_value is done by the function itself. You can only define from which
column to get the result. INDEX can be used to "lookup" in both directions,
left to right and right to left, but VLOOKUP can only be used from left to
right.

So, it depends on the application as to which is better. INDEX *might* be
slightly more efficient in certain applications but in my tests based on
your formula both methods produce identical calculation times.

=IF(ISERROR(VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE))

You can do a couple of things to shorten the formula. You can use a
different error trap and replace FALSE with 0.

=IF(COUNTIF(Gd.5U1!$B$14:$B$153,$B15),VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,0),"")

Note that COUNTIF evaluates numeric numbers and TEXT numbers to be equal. If
you're lookup up text strings, no problem.

However, if you're looking up numbers and have 2 different data types where
your lookup_value is a numeric number but the lookup_data is a TEXT number
(or vice versa) then the COUNTIF error trap will allow the VLOOKUP to
execute and could return a result of #N/A.
 
M

Max

... VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE))
... formula works but if a column is inserted it would skew the result

If you use the equivalent index/match for the above, viz:
=INDEX(Gd.5U1!$AA$14:$AA$153,MATCH($B15,Gd.5U1!$B$14:$B$153,0))
you'll find that "in-between" col insertions won't impact the expected
results as the return col AA will be auto-adjusted by Excel. And that, btw,
would be another advantage of using index/match.

If you need an error trap for unmatched cases, use this
=IF(ISNA(MATCH($B15,Gd.5U1!$B$14:$B$153,0)),"",INDEX(Gd.5U1!$AA$14:$AA$153,MATCH($B15,Gd.5U1!$B$14:$B$153,0)))

Celebrate success, hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
W

Wilma

Thank you so much for your help--the revised formula is exactly what I was
looking for. You all are the BEST!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top