| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| not sure if i need vlookup or match/index | Excel Worksheet Functions | |||
| VLOOKUP, MATCH, INDEX HELP! | Excel Worksheet Functions | |||
| VLOOKUP vs INDEX and MATCH | Excel Discussion (Misc queries) | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| Need Help with Index and Match or Vlookup | Excel Worksheet Functions | |||
Hybrid Mode
