VLOOKUP in VBA
 

Lance Hebert" wrote:

There will be a very large list of name, utlimately. Where could I go to get
assistance with that type of VBA code? Thanks for the help.


"Sean Timmons" wrote:

That would depend on how many people you'd be assigning.

you can use If...Then

=IF(AND(E2="X",J2="Y"),"Sue","Bob")

Or perhaps...

=IF(AND(E2="X",J2="Y"),"Sue",IF(AND(OR(E2="Z",E2=" N"),J2="Y"),"Bob","Charles")

You can nest several If statements, but if you're looking at a lot of names,
you'd want to look at doing Visual Basic code and a Select...Case...

"Lance Hebert" wrote:

I have a spreadsheet, which is shared on a network that has a number of
columns in it and a very large list of entries. I want to be able to add a
column that would look at a couple of the columns within each row and
determine, based on more criteria, who would be assigned to that particular
line. I would like the column to add the name of the person assigned based on
two other pieces of information in the column.

Here is the information:
Columns A-M, where Column E (Area) and Column J (Type) would be needed to
determine who would be assigned to this line. I know how to setup a VLOOKUP
for one item and attach a name to it from another sheet/tab, but how do I
make it so it would be similar to: If Column E is ... and Column J is ..., or
..., or .... than, Sue is assigned.

Any help is appreciated.

VLOOKUP in VBA
 

As long as the set of values is unique - ie, only one value has X and Y:

Dim Val1 As String
Dim Val2 As String
Dim myName As String
Dim myR As Long

Val1 = "X"
Val2 = "Y"

myR = Application.Evaluate("SumProduct((A1:A100=""" & Val1 & """)" & _
"*(B1:B100=""" & Val2 & """)*Row(B1:B100))")

myName = Range("C1:C100").Cells(myR, 1).Value
MsgBox myName


HTH,
Bernie
MS Excel MVP


"Lance Hebert" wrote in message
...
Lance Hebert" wrote:

There will be a very large list of name, utlimately. Where could I go to get
assistance with that type of VBA code? Thanks for the help.


"Sean Timmons" wrote:

That would depend on how many people you'd be assigning.

you can use If...Then

=IF(AND(E2="X",J2="Y"),"Sue","Bob")

Or perhaps...

=IF(AND(E2="X",J2="Y"),"Sue",IF(AND(OR(E2="Z",E2=" N"),J2="Y"),"Bob","Charles")

You can nest several If statements, but if you're looking at a lot of names,
you'd want to look at doing Visual Basic code and a Select...Case...

"Lance Hebert" wrote:

I have a spreadsheet, which is shared on a network that has a number of
columns in it and a very large list of entries. I want to be able to add a
column that would look at a couple of the columns within each row and
determine, based on more criteria, who would be assigned to that particular
line. I would like the column to add the name of the person assigned based on
two other pieces of information in the column.

Here is the information:
Columns A-M, where Column E (Area) and Column J (Type) would be needed to
determine who would be assigned to this line. I know how to setup a VLOOKUP
for one item and attach a name to it from another sheet/tab, but how do I
make it so it would be similar to: If Column E is ... and Column J is ..., or
..., or .... than, Sue is assigned.

Any help is appreciated.