Array Function with VLOOKUP
 

I hope someone can explain or help me with an array-function. Let me
first explain the situation:

I have a table where, using VLOOKUP I want to find values for multiple
items. After I have found the values, I want to have the minimum value of
this list.

This would result in 2 tables and 1 cell with the result:

Table 1 = data table (=A1:C100)
Table 2 = items where values are to be found for (=H1:H3)
Table 3 = results of VLOOKUP (=I1:I3)
'Table 4' = cell containing function 'MIN' for table 2 (=I4)

Now I would like to have these steps in one cell. I tried using an array
function, but it does not what I want it to do.

I used the following formula in cell I4 (created the formula using
CTRL+SHIFT+ENTER):

={MIN(VLOOKUP(H1:H3;A1:C100;3;FALSE))}

The result of this formula, unfortunately, is the value found in table 1
for the item in cell H1.

When I use this formula, but now in cells I1:I3 (selected all three
cells), and use CTRL+SHIFT+ENTER, I get three cells with the CORRECT
value! (I.e. I get the minimum value of the VLOOKUP results for the items
in cells H1:H3.)

Can someone tell me, that what I try to do, i.e. create a single-cell
formula, is possible using the VLOOKUP function in combination with an
array-function? Or perhaps someone knows a method of accomplishing this,
without the use of a temporary/'in between' table.

Please: no solutions using VBA, that is too easy! ;) I want to understand
the limitations of array-functions.

TIA,
CoRrRan

Start with the premise that vlookup will lookup A value, not values.

--
Don Guillett
SalesAid Software

"CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com wrote in message
...
I hope someone can explain or help me with an array-function. Let me
first explain the situation:

I have a table where, using VLOOKUP I want to find values for multiple
items. After I have found the values, I want to have the minimum value of
this list.

This would result in 2 tables and 1 cell with the result:

Table 1 = data table (=A1:C100)
Table 2 = items where values are to be found for (=H1:H3)
Table 3 = results of VLOOKUP (=I1:I3)
'Table 4' = cell containing function 'MIN' for table 2 (=I4)

Now I would like to have these steps in one cell. I tried using an array
function, but it does not what I want it to do.

I used the following formula in cell I4 (created the formula using
CTRL+SHIFT+ENTER):

={MIN(VLOOKUP(H1:H3;A1:C100;3;FALSE))}

The result of this formula, unfortunately, is the value found in table 1
for the item in cell H1.

When I use this formula, but now in cells I1:I3 (selected all three
cells), and use CTRL+SHIFT+ENTER, I get three cells with the CORRECT
value! (I.e. I get the minimum value of the VLOOKUP results for the items
in cells H1:H3.)

Can someone tell me, that what I try to do, i.e. create a single-cell
formula, is possible using the VLOOKUP function in combination with an
array-function? Or perhaps someone knows a method of accomplishing this,
without the use of a temporary/'in between' table.

Please: no solutions using VBA, that is too easy! ;) I want to understand
the limitations of array-functions.

TIA,
CoRrRan

Unfortunately, VLOOKUP does not accept an array of lookup values.
However, LOOKUP does...

=MIN(LOOKUP(H1:H3,A1:A100,C1:C100)), or

=MIN(IF(LOOKUP(H1:H3,A1:A3,C1:C3)0,LOOKUP(H1:H3,A 1:A3,C1:C3))) to
exclude zero values,

*Note that Column A needs to be sorted in ascending order. Also, if an
exact match is not found, LOOKUP will return an approximate one. So if
this isn't going to be appropriate for your needs, you can try the
following...

=MIN((ISNUMBER(MATCH(A1:A100,H1:H3,0)))*C1:C100), or

=MIN(IF((ISNUMBER(MATCH(A1:A100,H1:H3,0)))*(C1:C10 00),C1:C100)) to
exclude zero values

*Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

In article ,
CoRrRan <CoRrRan~~[at]~~gmail~~[dot]~~com wrote:

I hope someone can explain or help me with an array-function. Let me
first explain the situation:

I have a table where, using VLOOKUP I want to find values for multiple
items. After I have found the values, I want to have the minimum value of
this list.

This would result in 2 tables and 1 cell with the result:

Table 1 = data table (=A1:C100)
Table 2 = items where values are to be found for (=H1:H3)
Table 3 = results of VLOOKUP (=I1:I3)
'Table 4' = cell containing function 'MIN' for table 2 (=I4)

Now I would like to have these steps in one cell. I tried using an array
function, but it does not what I want it to do.

I used the following formula in cell I4 (created the formula using
CTRL+SHIFT+ENTER):

={MIN(VLOOKUP(H1:H3;A1:C100;3;FALSE))}

The result of this formula, unfortunately, is the value found in table 1
for the item in cell H1.

When I use this formula, but now in cells I1:I3 (selected all three
cells), and use CTRL+SHIFT+ENTER, I get three cells with the CORRECT
value! (I.e. I get the minimum value of the VLOOKUP results for the items
in cells H1:H3.)

Can someone tell me, that what I try to do, i.e. create a single-cell
formula, is possible using the VLOOKUP function in combination with an
array-function? Or perhaps someone knows a method of accomplishing this,
without the use of a temporary/'in between' table.

Please: no solutions using VBA, that is too easy! ;) I want to understand
the limitations of array-functions.

TIA,
CoRrRan

Correction...

=MIN(IF(ISNUMBER(MATCH(A1:A3,H1:H3,0)),C1:C3))

....confirmed with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

=MIN((ISNUMBER(MATCH(A1:A100,H1:H3,0)))*C1:C100)

Domenic wrote in news:domenic22-
:

Correction...

=MIN(IF(ISNUMBER(MATCH(A1:A3,H1:H3,0)),C1:C3))

...confirmed with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

=MIN((ISNUMBER(MATCH(A1:A100,H1:H3,0)))*C1:C100)

Dominic, thank you very much! This function works perfectly. (However, for
the sake of the example: A1:A3 and C1:C3 should be A1:A100 and C1:C100.)

Many thanks for pointing me to the LOOKUP-function itself. I'll try to use
this function somewhat more often.

And the second formula also results in the correct answer. And I do need
the exact values. But your examples provide enough basis for me to continue
this!

Many thanks!

CoRrRan

In article ,
CoRrRan [orthis] wrote:

Dominic, thank you very much!

You're very welcome!

...for the sake of the example: A1:A3 and C1:C3 should be A1:A100 and C1:C100.

In correcting the formula, I inadvertently picked up the wrong ranges. :)

Don Guillett wrote:
Start with the premise that vlookup will lookup A value, not values.

Nonsense.

Alan Beban

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

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

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

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

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

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

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

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

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