Robin,

Having the function in your Personal.xls is OK, but the workbook where you
want to use it either needs a reference (through the VBE) to the
personal.xls, or you need to enter it like

=Personal.xls!MVLookup(.....)

Just so you know for future use - since I see that you figured it out,
presumably with a codemodule in the workbook where you want to use it.

Bernie

"Robin" wrote in message
...
Nope. It's in a standard module in my personal.xls
Any other ideas?

"Bernie Deitrick" wrote:

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!!