define a range that equals the first row of another range
 

I want to create my own function that is similar to vlookup, but also uses
match function.

My understanding is that there is no way to embed worksheet functions within
another worksheet function, so I will have to perform the 'match' and
'vlookup' functions separately.

In only want to have to import one lookup range for both the vlookup and
match functions. Since the vlookup range will include the match range, I am
looking for a way to extract the first row.

That is match-range equals first-row-of-vlookup-range.

How can I define a range that equals the first row of another range?


--
Richard

define a range that equals the first row of another range
 

Hi,

You can embed worksheetfunctions in VB and here's an index-match example

myvalue = WorksheetFunction.Index(Range("D1:D15"),
WorksheetFunction.Match(Range("H1"), Range("B1:B15")))

Perhaps you could explain a bit more clearly what you are trying to do and
I'm sure someone will help.

Mike

"Richard" wrote:

I want to create my own function that is similar to vlookup, but also uses
match function.

My understanding is that there is no way to embed worksheet functions within
another worksheet function, so I will have to perform the 'match' and
'vlookup' functions separately.

In only want to have to import one lookup range for both the vlookup and
match functions. Since the vlookup range will include the match range, I am
looking for a way to extract the first row.

That is match-range equals first-row-of-vlookup-range.

How can I define a range that equals the first row of another range?


--
Richard

define a range that equals the first row of another range
 

Mike,
You solved first problem I am having with the embeded function.
My Excel book indicated I should use 'application.worksheetfunction' not
just 'worksheetfunction'. The longer expression does not embed.

The problem I'm still having is that I would like to only have to pass one
range through my function, something like this:

sub function modified_vlookup(input_value, search_range, column_heading)
modified_lookup= vlookup(input_value, search_range, match(column_heading,_
first-column-of-search_range)
end function

So you see I still need a way of coming up with
first-column-of-search_range

--
Richard


"Mike H" wrote:

Hi,

You can embed worksheetfunctions in VB and here's an index-match example

myvalue = WorksheetFunction.Index(Range("D1:D15"),
WorksheetFunction.Match(Range("H1"), Range("B1:B15")))

Perhaps you could explain a bit more clearly what you are trying to do and
I'm sure someone will help.

Mike

"Richard" wrote:

I want to create my own function that is similar to vlookup, but also uses
match function.

My understanding is that there is no way to embed worksheet functions within
another worksheet function, so I will have to perform the 'match' and
'vlookup' functions separately.

In only want to have to import one lookup range for both the vlookup and
match functions. Since the vlookup range will include the match range, I am
looking for a way to extract the first row.

That is match-range equals first-row-of-vlookup-range.

How can I define a range that equals the first row of another range?


--
Richard

define a range that equals the first row of another range
 

Richard wrote:
Mike,
You solved first problem I am having with the embeded function.
My Excel book indicated I should use 'application.worksheetfunction' not
just 'worksheetfunction'. The longer expression does not embed.

The problem I'm still having is that I would like to only have to pass one
range through my function, something like this:

sub function modified_vlookup(input_value, search_range, column_heading)
modified_lookup= vlookup(input_value, search_range, match(column_heading,_
first-column-of-search_range)
end function

So you see I still need a way of coming up with
first-column-of-search_range

Application.Index(search_range, 0, 1)

Alan

define a range that equals the first row of another range
 

Alan,
Sorry for not being clearer.

Assume I need to do a vlookup and a match statement in the same user defined
function.

If I imput the vlookup_lookup_range (a 2-D) array, I will need either the
first row or first column of vlookup_lookup_range to do the Match function.

So how do I get range for the, say, the first row from vlookup_lookup_range?

--
Richard


"Alan Beban" wrote:

Richard wrote:
Mike,
You solved first problem I am having with the embeded function.
My Excel book indicated I should use 'application.worksheetfunction' not
just 'worksheetfunction'. The longer expression does not embed.

The problem I'm still having is that I would like to only have to pass one
range through my function, something like this:

sub function modified_vlookup(input_value, search_range, column_heading)
modified_lookup= vlookup(input_value, search_range, match(column_heading,_
first-column-of-search_range)
end function

So you see I still need a way of coming up with
first-column-of-search_range

Application.Index(search_range, 0, 1)

Alan