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