Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to simplify vlookup formula
I want to have custom functions that will simplify vlookups (and other
formulas) which have imbedded "if" formulas to eliminate errors. e.g.This formula provided in a previous posting is ugly and somewhat annoying: =IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2 ,Stock,2,0)) Can I replace it with a custom function that works the same but looks something like this: =MyVLookup($A2,Stock,2,0) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to simplify vlookup formula
Public Function myLookup(lookup_value, _
table_array As Range, _ col_index_num As Long, _ Optional range_lookup As Boolean = True) Dim tmp On Error Resume Next tmp = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup) On Error GoTo 0 If IsError(tmp) Then myLookup = "" Else myLookup = tmp End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "John James" wrote in message ... I want to have custom functions that will simplify vlookups (and other formulas) which have imbedded "if" formulas to eliminate errors. e.g.This formula provided in a previous posting is ugly and somewhat annoying: =IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2 ,Stock,2,0)) Can I replace it with a custom function that works the same but looks something like this: =MyVLookup($A2,Stock,2,0) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to simplify vlookup formula
Many thanks, Bob.
With the tmp equation all on one line it looks like it works like a charm!!! I'll be using this on various other XL formulas to eliminate the need for those annoying if(iserror(... portions of formulae. Beautiful!!!! Cheers, "Bob Phillips" wrote in message ... Public Function myLookup(lookup_value, _ table_array As Range, _ col_index_num As Long, _ Optional range_lookup As Boolean = True) Dim tmp On Error Resume Next tmp = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup) On Error GoTo 0 If IsError(tmp) Then myLookup = "" Else myLookup = tmp End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "John James" wrote in message ... I want to have custom functions that will simplify vlookups (and other formulas) which have imbedded "if" formulas to eliminate errors. e.g.This formula provided in a previous posting is ugly and somewhat annoying: =IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2 ,Stock,2,0)) Can I replace it with a custom function that works the same but looks something like this: =MyVLookup($A2,Stock,2,0) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to simplify vlookup formula
Hello,
I try this and work perfectly!!!. Do you know how to custom this in the same way: =IF(ISERROR(INDEX(A1:C3,MATCH(D2,A1:A3,),MATCH(E1, A1:C1,))),"",INDEX(A1:C3,MATCH(D2,A1:A3,),MATCH(E1 ,A1:C1,))) Thanks in advance "Bob Phillips" wrote: Public Function myLookup(lookup_value, _ table_array As Range, _ col_index_num As Long, _ Optional range_lookup As Boolean = True) Dim tmp On Error Resume Next tmp = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup) On Error GoTo 0 If IsError(tmp) Then myLookup = "" Else myLookup = tmp End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "John James" wrote in message ... I want to have custom functions that will simplify vlookups (and other formulas) which have imbedded "if" formulas to eliminate errors. e.g.This formula provided in a previous posting is ugly and somewhat annoying: =IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2 ,Stock,2,0)) Can I replace it with a custom function that works the same but looks something like this: =MyVLookup($A2,Stock,2,0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simplify VLOOKUP with conditions | Excel Worksheet Functions | |||
Custom function to simplify Index(match)) formula | Excel Discussion (Misc queries) | |||
Custom Function VLookup | Excel Discussion (Misc queries) | |||
simplify a vlookup function | Excel Worksheet Functions | |||
Simplify Vlookup function in Excel | Excel Worksheet Functions |