Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old April 6th 05, 12:15 PM
Domenic
 
Posts: n/a
Default

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   Report Post  
Old April 6th 05, 09:49 PM
Alan Beban
 
Posts: n/a
Default

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   Report Post  
Old April 6th 05, 09:54 PM
Alan Beban
 
Posts: n/a
Default

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   Report Post  
Old April 6th 05, 10:44 PM
Domenic
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 12:00 PM
CoRrRan
 
Posts: n/a
Default

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
Vlookup-function. I then assumed that it must be possible to use the
Vlookup-function in an singel-cell-array-style, 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 Vlookup-function returns something else than an array?
Quite strange this is, as Dominic also states in one of his e-mails: 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 3-cell column returns the
values from Column B corresponding to the values in A1:A3.

Alan Beban




  #16   Report Post  
Old April 8th 05, 05:54 PM
Alan Beban
 
Posts: n/a
Default

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 built-in function
through VBA, I'd be more inclined to think that the built-in 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
Vlookup-function. I then assumed that it must be possible to use the
Vlookup-function in an singel-cell-array-style, 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 Vlookup-function returns something else than an array?
Quite strange this is, as Dominic also states in one of his e-mails: 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 3-cell column returns the
values from Column B corresponding to the values in A1:A3.

Alan Beban





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify Vlookup function in Excel Budman Excel Worksheet Functions 7 March 27th 05 04:17 PM
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 03:47 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
carrying a hyper link when using the vlookup function mike Excel Worksheet Functions 1 November 19th 04 04:49 AM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017