VLookup vs. Match/Index
 

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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

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

--
__________________________________
HTH

Bob

"Wilma" wrote in message
...
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

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.

--
Biff
Microsoft Excel MVP


"Wilma" wrote in message
...
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

... 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)),"",IND EX(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
---
"Wilma" wrote:
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

Thank you for taking the time to explain the differences--it was a big help!

"T. Valko" wrote:

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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

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.

--
Biff
Microsoft Excel MVP


"Wilma" wrote in message
...
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

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

"Max" wrote:

... 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)),"",IND EX(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
---
"Wilma" wrote:
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Wilma" wrote in message
...
Thank you for taking the time to explain the differences--it was a big
help!

"T. Valko" wrote:

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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

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.

--
Biff
Microsoft Excel MVP


"Wilma" wrote in message
...
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,F ALSE)),"",VLOOKUP($B15,Gd.5U1!$B$14:$BC$153,26,FAL SE))

VLookup vs. Match/Index
 

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Wilma" wrote in message
...
Thank you so much for your help--the revised formula is exactly what I was
looking for. You all are the BEST!