Home 
Search 
Today's Posts 
#11




Interesting...
Thanks Alan! In article , Alan Beban wrote: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban 
#12




Further, as long as the values in A1:A3 are unique, then in VBA
Application.Min(Application.VLookup(Range("A1:A3") ,Range("A1:B8"),2, 0)) will return the expected value. This also works with MAX and SUM, but apparently not with Application.Large(Application.VLookup(Range("A1:A3 "), Range("A1:B8"), 2, 0),2) Oh well. Alan Beban Domenic wrote: Interesting... Thanks Alan! In article , Alan Beban wrote: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban 
#13




And
Typename(Application.VLookup(Range("A1:A3"), Range("A1:B8"), 2, 0)) returns Variant() Alan Beban Domenic wrote: Interesting... Thanks Alan! In article , Alan Beban wrote: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban 
#14




I'm finding these inconsistencies rather amusing. But I'm sure that at
some point in time Microsoft will rectify them. Hopefully sooner rather than later. In article , Alan Beban wrote: Further, as long as the values in A1:A3 are unique, then in VBA Application.Min(Application.VLookup(Range("A1:A3") ,Range("A1:B8"),2, 0)) will return the expected value. This also works with MAX and SUM, but apparently not with Application.Large(Application.VLookup(Range("A1:A3 "), Range("A1:B8"), 2, 0),2) Oh well. Alan Beban Domenic wrote: Interesting... Thanks Alan! In article , Alan Beban wrote: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban 
#15




Yes, I did find this behaviour in my spreadsheet. When using the =Min
(Vlookup(...function and CTRL+SHIFT+ENTER it in multiple cells, it returned the SAME minimum value of the items in the first argument of the Vlookupfunction. I then assumed that it must be possible to use the Vlookupfunction in an singelcellarraystyle, but this didn't have the correct effect. So you assume (or others as you mention in the post which I am replying to now) that the Vlookupfunction returns something else than an array? Quite strange this is, as Dominic also states in one of his emails: it looks like a bug. Well, anyway, thank you for your help! We now have a rather neat little function in our calculation. CoRrRan Alan Beban wrote in news:#2jXBXlOFHA.3076 @tk2msftngp13.phx.gbl: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban Domenic wrote: Yes, my statement was incorrect. Thanks Alan! While VLOOKUP does accept an array of lookup values, it doesn't seem to pass that array to a subsequent function, as in the following... {=MIN(VLOOKUP(A1:A3,A1:B8,2,0))} In article , Alan Beban wrote: Domenic wrote: Unfortunately, VLOOKUP does not accept an array of lookup values. ?? =VLOOKUP(a1:a3,a1:b8,2,0) array entered in a 3cell column returns the values from Column B corresponding to the values in A1:A3. Alan Beban 
#16




Still assuming that the values in the lefthand column of the table_array
are uniques, it seems one can get the correct results by entering (*without* array entering, though that seems to work as well) into a single cell with the following UDF instead of the built_in VLOOKUP function(watch for word wrap in this posting): Function ArrayVlookup(lookup_value, table_array, column_reference, range_lookup) ArrayVlookup = Application.VLookup(lookup_value, table_array, column_reference, range_lookup) End Function E.g., =MAX(ArrayVlookup,A1:A3,A1:B8,0) FWIW, I have never understood what was meant by "the VLOOKUP function returns something different from an array". Based on the different results achievable on the worksheet by running the builtin function through VBA, I'd be more inclined to think that the builtin function has a bug that VBA bypasses or corrects for, than that it returns "something different from an array". But who knows? I certainly don't know anything about the inner workings of the codes. Alan Beban CoRrRan wrote: Yes, I did find this behaviour in my spreadsheet. When using the =Min (Vlookup(...function and CTRL+SHIFT+ENTER it in multiple cells, it returned the SAME minimum value of the items in the first argument of the Vlookupfunction. I then assumed that it must be possible to use the Vlookupfunction in an singelcellarraystyle, but this didn't have the correct effect. So you assume (or others as you mention in the post which I am replying to now) that the Vlookupfunction returns something else than an array? Quite strange this is, as Dominic also states in one of his emails: it looks like a bug. Well, anyway, thank you for your help! We now have a rather neat little function in our calculation. CoRrRan Alan Beban wrote in news:#2jXBXlOFHA.3076 @tk2msftngp13.phx.gbl: There was a discussion of this a few months back in this forum. There seems to be some thought that what the VLOOKUP function returns is something other than an array. For what it's worth, if you were to array enter =MIN(VLOOKUP(A1:A3,A1:B8,2,0) into two or more cells, it would in fact return the expected minimum value to each cell. Alan Beban Domenic wrote: Yes, my statement was incorrect. Thanks Alan! While VLOOKUP does accept an array of lookup values, it doesn't seem to pass that array to a subsequent function, as in the following... {=MIN(VLOOKUP(A1:A3,A1:B8,2,0))} In article , Alan Beban wrote: Domenic wrote: Unfortunately, VLOOKUP does not accept an array of lookup values. ?? =VLOOKUP(a1:a3,a1:b8,2,0) array entered in a 3cell column returns the values from Column B corresponding to the values in A1:A3. Alan Beban 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Simplify Vlookup function in Excel  Excel Worksheet Functions  
How to use a cell value as Table Array in VLOOKUP worksheet function  Excel Discussion (Misc queries)  
Match / Vlookup within an Array formula  Excel Discussion (Misc queries)  
Formula to list unique values  Excel Worksheet Functions  
carrying a hyper link when using the vlookup function  Excel Worksheet Functions 