VLookup vs. Match/Index

  • Thread starter Thread starter Wilma
  • Start date Start date
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))
 
MATCH INDEX can look left as well as looking right, but is not better, in
fact is two function calls rather than 1.
 
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.
 
... VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FALSE))
... formula works but if a column is inserted it would skew the result
Click to expand...

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
---
 
Thank you so much for your help--the revised formula is exactly what I was
looking for. You all are the BEST!
 
Back
Top