I have a data range called Benefits where the top row is named BenefitHeader.
(Benefits does not include the top row). The data looks something like this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will be
appreciated!!

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will
be
appreciated!!

With that solution I have to have named ranges for every column that I want
to use then, right? Since the columns from A through IE are used, that could
be a lot of named ranges, and it doesn't allow for the flexibility of looking
up different values based on different columns that I was hoping for. Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will
be
appreciated!!

If indeed the you have a table of that size I suspect an array formula
will be extremely slow but you can include index and the named table
(Benefits)
but if you want this in one fell swoop you would need an array formula
which will probably slow down the workbook of that size to crawling.


--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I
want
to use then, right? Since the columns from A through IE are used, that
could
be a lot of named ranges, and it doesn't allow for the flexibility of
looking
up different values based on different columns that I was hoping for.
Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the
other
columns within the named range (Currently, the columns used are A:IE
and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1
and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings
for
values I want to specify and the column heading for the value that I
would
like to get.
I think it must be possible but I don't know how to do it. Any help
will
be
appreciated!!

Robin,

Try the UDF below, which can be used like this (with values, in this cas all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value pairs (you can have as many
pairs as you like) and the last is the header of the value that you want to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I want
to use then, right? Since the columns from A through IE are used, that could
be a lot of named ranges, and it doesn't allow for the flexibility of looking
up different values based on different columns that I was hoping for. Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will
be
appreciated!!

Thanks, Bernie! This is very close to what I was envisioning but I'm having
difficulty getting it to work. It's probably something really simple that
I'm doing wrong, but I am getting #NAME when I use the formula. I have
entered the code just as you had it and I have tried adjusting my named range
to include the header row vs not include the header row, and I have tried
using quotations vs not using quotations, and all come up with the same
result. Any thoughts on what I'm doing wrong?

=MVLookup(benefits, "Benefit_Name", "SRP Plus", "Policy_Subtype", "Section
162", "Policy_Owner_optionkey")

This is the formula I have entered. benefits is my named range for the data
table. "Benefit_Name" is the header, "SRP Plus" is what I'm looking for in
that column. "Policy_Subtype" is the next header and "Section 162" is what
I'm looking for in that column. I want to see the corresponding value from
"Policy_Owner_Optionkey" column. This is exactly the type of syntax I was
hoping for, so if we can get this to work I will be very grateful you. :-)
It's something I will be able to use over and over in my work.

"Bernie Deitrick" wrote:

Robin,

Try the UDF below, which can be used like this (with values, in this cas all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value pairs (you can have as many
pairs as you like) and the last is the header of the value that you want to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I want
to use then, right? Since the columns from A through IE are used, that could
be a lot of named ranges, and it doesn't allow for the flexibility of looking
up different values based on different columns that I was hoping for. Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will
be
appreciated!!

Robin,

You seem to have put the code into either the ThisWorkbook's codemodule, a
sheet's codemodule, or into a class module. It must go into a standard
module. That is the usual source of the #NAME error.

Bernie

"Robin" wrote in message
...
Thanks, Bernie! This is very close to what I was envisioning but I'm
having
difficulty getting it to work. It's probably something really simple that
I'm doing wrong, but I am getting #NAME when I use the formula. I have
entered the code just as you had it and I have tried adjusting my named
range
to include the header row vs not include the header row, and I have tried
using quotations vs not using quotations, and all come up with the same
result. Any thoughts on what I'm doing wrong?

=MVLookup(benefits, "Benefit_Name", "SRP Plus", "Policy_Subtype", "Section
162", "Policy_Owner_optionkey")

This is the formula I have entered. benefits is my named range for the
data
table. "Benefit_Name" is the header, "SRP Plus" is what I'm looking for
in
that column. "Policy_Subtype" is the next header and "Section 162" is
what
I'm looking for in that column. I want to see the corresponding value
from
"Policy_Owner_Optionkey" column. This is exactly the type of syntax I was
hoping for, so if we can get this to work I will be very grateful you.
:-)
It's something I will be able to use over and over in my work.

"Bernie Deitrick" wrote:

Robin,

Try the UDF below, which can be used like this (with values, in this cas
all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return
Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value
pairs (you can have as many
pairs as you like) and the last is the header of the value that you want
to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As
Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I
want
to use then, right? Since the columns from A through IE are used, that
could
be a lot of named ranges, and it doesn't allow for the flexibility of
looking
up different values based on different columns that I was hoping for.
Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something
like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the
other
columns within the named range (Currently, the columns used are A:IE
and
more
could be added at any time). To do a single lookup value I would
use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is
1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column
headings for
values I want to specify and the column heading for the value that I
would
like to get.
I think it must be possible but I don't know how to do it. Any help
will
be
appreciated!!

Oh, and the named range needs to include the header row, since the code
looks there for the match of header values.

Bernie

"Robin" wrote in message
...
Thanks, Bernie! This is very close to what I was envisioning but I'm
having
difficulty getting it to work. It's probably something really simple that
I'm doing wrong, but I am getting #NAME when I use the formula. I have
entered the code just as you had it and I have tried adjusting my named
range
to include the header row vs not include the header row, and I have tried
using quotations vs not using quotations, and all come up with the same
result. Any thoughts on what I'm doing wrong?

=MVLookup(benefits, "Benefit_Name", "SRP Plus", "Policy_Subtype", "Section
162", "Policy_Owner_optionkey")

This is the formula I have entered. benefits is my named range for the
data
table. "Benefit_Name" is the header, "SRP Plus" is what I'm looking for
in
that column. "Policy_Subtype" is the next header and "Section 162" is
what
I'm looking for in that column. I want to see the corresponding value
from
"Policy_Owner_Optionkey" column. This is exactly the type of syntax I was
hoping for, so if we can get this to work I will be very grateful you.
:-)
It's something I will be able to use over and over in my work.

"Bernie Deitrick" wrote:

Robin,

Try the UDF below, which can be used like this (with values, in this cas
all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return
Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value
pairs (you can have as many
pairs as you like) and the last is the header of the value that you want
to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As
Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I
want
to use then, right? Since the columns from A through IE are used, that
could
be a lot of named ranges, and it doesn't allow for the flexibility of
looking
up different values based on different columns that I was hoping for.
Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something
like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the
other
columns within the named range (Currently, the columns used are A:IE
and
more
could be added at any time). To do a single lookup value I would
use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is
1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column
headings for
values I want to specify and the column heading for the value that I
would
like to get.
I think it must be possible but I don't know how to do it. Any help
will
be
appreciated!!

Hi, Bernie -
One more question. Is there a way to modify this formula (which I love, by
the way)and have one of the parameters look for a blank value? I'm trying
everything I can think of and I can't get that to work, so I thought I'd
check with you to see if it's possible.
Again, I really appreciate your help!

"Bernie Deitrick" wrote:

Robin,

Try the UDF below, which can be used like this (with values, in this cas all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value pairs (you can have as many
pairs as you like) and the last is the header of the value that you want to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I want
to use then, right? Since the columns from A through IE are used, that could
be a lot of named ranges, and it doesn't allow for the flexibility of looking
up different values based on different columns that I was hoping for. Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the other
columns within the named range (Currently, the columns used are A:IE and
more
could be added at any time). To do a single lookup value I would use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is 1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column headings for
values I want to specify and the column heading for the value that I would
like to get.
I think it must be possible but I don't know how to do it. Any help will
be
appreciated!!

Robin,

Something like

=MVLookup(C4:G1000,"Header 1","","Header 2","Value 2","Return Header")

as compared to

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return
Header")

will work IF the blanks are actually filled with single quote marks. If they
are not, use Edit Go To.. Special Blanks, then press a single quote mark
and press Ctrl-Enter - that will fill the blanks with single qoutes to get
the function to work.

HTH,
Bernie

"Robin" wrote in message
...
Hi, Bernie -
One more question. Is there a way to modify this formula (which I love,
by
the way)and have one of the parameters look for a blank value? I'm trying
everything I can think of and I can't get that to work, so I thought I'd
check with you to see if it's possible.
Again, I really appreciate your help!

"Bernie Deitrick" wrote:

Robin,

Try the UDF below, which can be used like this (with values, in this cas
all strings, but they can
be anything)

=MVLookup(C4:G1000,"Header 1","Value 1","Header 2","Value 2","Return
Header")

Or cell references:
=MVLookup(C4:G1000,G2,G1,D2,D1,E2)

The first parameter is the entire table, the next pairs are header/value
pairs (you can have as many
pairs as you like) and the last is the header of the value that you want
to return. The function
will return the first valid value found.

HTH,
Bernie
MS Excel MVP


Function MVLOOKUP(myR As Range, ParamArray Params() As Variant) As
Variant
Dim ErrMsg As String
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long

On Error GoTo ErrHandler
For i = LBound(Params) To UBound(Params) - 2 Step 2
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
ErrMsg = "Entry Match Failed"
n = Application.Match(Params(i + 1), myR.Columns(m).Cells, False)
Next i
i = UBound(Params)
ErrMsg = "Header Match Failed"
m = Application.Match(Params(i), myR.Rows(1).Cells, False)

'Find the first Value
MVLOOKUP = "No Match"
n = Application.Match(Params(UBound(Params)), myR.Rows(1).Cells, False)

For j = 2 To myR.Rows.Count
For i = LBound(Params) To UBound(Params) - 2 Step 2
m = Application.Match(Params(i), myR.Rows(1).Cells, False)
If myR.Cells(j, m) < Params(i + 1) Then GoTo NoMatch:
Next i
MVLOOKUP = myR.Cells(j, n)
NoMatch:
Next j

Exit Function
ErrHandler:
MVLOOKUP = ErrMsg
End Function



"Robin" wrote in message
...
With that solution I have to have named ranges for every column that I
want
to use then, right? Since the columns from A through IE are used, that
could
be a lot of named ranges, and it doesn't allow for the flexibility of
looking
up different values based on different columns that I was hoping for.
Hmmm.
Any other ideas?

"Peo Sjoblom" wrote:

=INDEX(Terminated_range,MATCH(1,(Name_range="SRP") *(Var1_Range="S162")*(Owner_range=1),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom



"Robin" wrote in message
...
I have a data range called Benefits where the top row is named
BenefitHeader.
(Benefits does not include the top row). The data looks something
like
this:

Name Owner Terminated Var1
LTC 0 12/31/2007 TRUE
LTC 0 TRUE
SRP 1 12/31/2007 EP
SRP 0 EP
SRP 1 S162
SRP 0 S162

I need to be able to lookup based on Name and any one or two of the
other
columns within the named range (Currently, the columns used are A:IE
and
more
could be added at any time). To do a single lookup value I would
use
=VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0))
and that would get the first instance of LTC and pull the terminated
value.
However, how would I get the terminated value for SRP where owner is
1 and
Var1 is S162? I'd like to do this without concatenating columns, if
possible, because I want to keep it flexible when determining which
columns
to use. I am open to adding a UDF where I pass it the column
headings for
values I want to specify and the column heading for the value that I
would
like to get.
I think it must be possible but I don't know how to do it. Any help
will
be
appreciated!!