Lookup Question with multiple dynamic variables

[Robin]

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",BenefitHeader,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!!

 

[Peo Sjoblom]

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

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

 

[Robin]

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]

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

 

[Bernie Deitrick]

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]

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]

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

 

[Bernie Deitrick]

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

Bernie

 

[Robin]

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

 

[Robin]

I moved the code to another module and it looks like it's working. Thank
you, thank you, thank you!!! I really appreciate your help. Have a great
weekend.

 

[Bernie Deitrick]

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]

Ahhhh.. thanks again!

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

 

[Peo Sjoblom]

If you want to make it available without having to use Personal.xls you can
save it as an add-in (*.xla)
Then you can select it under tools>add-ins and it will be available for all
new workbooks


--


Regards,


Peo Sjoblom

 

[Robin]

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]

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