HI all!! Thank you for time and knowledge. I am looking for a vlookup to search multiple criteria for one result?

Below is my workbook with 2 sheets. The "Name" column in "Sheet 1" is blank. This is where I want to put the vlookup formula. The "Answer should be:" doesn't exist. It just for reference to check the results.

I am looking for a vlookup formula that will lookup the "Location Code" and the "Task" in "Sheet 1" and compare it to "Sheet 2" to give me the result from "Sheet 2". I am looking for the name of the Location/Task. The only unique value is the Task (the location can be duplicated many times and the different location codes can have the same task as other location codes).

Sheet 1
Name Location Code Task Answer should be:
{Formula Here} IL001 010 Bob
{Formula Here} IL001 009 Bill
{Formula Here} IL002 008 Diane
{Formula Here} IL002 007 Tina
{Formula Here} IL003 006 Bill
{Formula Here} IL004 005 Tina
{Formula Here} IL004 004 Bob
{Formula Here} IL005 003 Diane
{Formula Here} IL006 002 Tina
{Formula Here} IL007 001 Bob
{Formula Here} IL008 000 Diane


Sheet 2
Location Code Task Name
IL008 000 Diane
IL007 001 Bob
IL006 002 Tina
IL005 003 Diane
IL004 004 Bob
IL004 005 Tina
IL003 006 Bill
IL002 007 Tina
IL002 008 Diane
IL001 009 Bill
IL001 010 Bob

I have tried many different options to no avail. One simple option I thought for sure would work is: =VLOOKUP(B2&C2,name,3,FALSE)
{I named the range "name"}

I have also attached the information.

Thank you so much for your time and I look forward to seeing the results.

hi,

Am Mon, 11 Mar 2013 21:10:34 +0000 schrieb srm6:

Sheet 1
Name Location Code Task Answer should be:
{Formula Here} IL001 010 Bob
{Formula Here} IL001 009 Bill
{Formula Here} IL002 008 Diane
{Formula Here} IL002 007 Tina
{Formula Here} IL003 006 Bill
{Formula Here} IL004 005 Tina
{Formula Here} IL004 004 Bob
{Formula Here} IL005 003 Diane
{Formula Here} IL006 002 Tina
{Formula Here} IL007 001 Bob
{Formula Here} IL008 000 Diane

Sheet 2
Location Code Task Name
IL008 000 Diane
IL007 001 Bob
IL006 002 Tina
IL005 003 Diane
IL004 004 Bob
IL004 005 Tina
IL003 006 Bill
IL002 007 Tina
IL002 008 Diane
IL001 009 Bill
IL001 010 Bob

try:
=INDEX(Sheet2!$C$2:$C$200,MATCH(B2&C2,Sheet2!$A$2: $A$200&Sheet2!$B$2:$B$200,0))
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi srm6

You want to invert your data!

Assuming data is on Sheet2 A1:C12 including column headers.
On sheet1 in A2 copy across to C2 & down to C12:

=INDEX(Sheet2!$A$2:$C$12,ROWS(2:$12),COLUMNS($A:A) )

Kevin

Thank you Claus Busch. This worked perfectly. However, when I put it into my spreadsheet consisting of 19804 rows it bogged down the report and took forever for me to run the existing macro I have in the report. Is there a way to add this to an existing macro so it will run faster?

My existing macro is as follows:

Sub CopyData3()
Application.ScreenUpdating = False
Dim vNames, vSheets, lLastRow&, n&, c As Range
Const sNames$ = "brad,casey,dave,dott,jason,jesus,rick,russ"
Const sSheets$ = "brad,casey,dave,dott,jason,jesus,rick,russ"
vNames = Split(sNames, ","): vSheets = Split(sSheets, ",")
lLastRow = Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Raw Data")
For n = LBound(vNames) To UBound(vNames)
For Each c In .Range("A2:A" & lLastRow)
If c = vNames(n) And c.Offset(0, 4) <= Date _
And c.Offset(0, 6) = "" Then
.Range(Cells(c.Row, 1), Cells(c.Row, 5)).Copy _
Sheets(vSheets(n)).Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
End If 'c = vNames(n)
Next 'c
Next 'n
End With 'Sheets("Raw Data")
Application.ScreenUpdating = True
End Sub 'CopyData3

The macro is taking my raw data and splitting the information up into seperate sheets based on the date and the user name.

The formula will only exist in the raw data. The copydata macro will then use that information to determine what data goes into which sheet.

Eventually I would like the macro to create the sheet for each user but for now this one works. I just need to get that formula into the macro. Thanks for your help.

Thanks Kevin. This formula worked perfectly for the information I provided to you however, when I entered it into my massive spreadsheet (19804 rows) and tweaked a few things here and there to match the columns it didn't work properly. Can you explain the steps in the formula so I can see where I tweaked it incorrectly?

The formula is in my "Raw Data" sheet column A.

It is pulling information from my "Column and Sheet Names" sheet columns G, H, I.

This is the formula after I tweaked it.

=INDEX('Column and Sheet Names'!$I$2:$I$19804,MATCH(B2&C2,'Column and Sheet Names'!$G$2:$G$19804&'Column and Sheet Names'!$H$2:$H$19804,0))

Perhaps you just want to copy the 'Value's!, perhaps!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

What's making it takes so long is that it reads/writes directly to the
worksheet[s]. This will always be slower than dumping the range[s] into
an array and process in memory first, then dump the data into the
target worksheet.

In this case each name's data would have to be put into a temporary
array and dumped to the respective sheet in turn. This would be a bit
more than trivial and so will take some time...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi,

Am Tue, 12 Mar 2013 16:15:53 +0000 schrieb srm6:

This worked perfectly. However, when I put it
into my spreadsheet consisting of 19804 rows it bogged down the report
and took forever for me to run the existing macro I have in the report.
Is there a way to add this to an existing macro so it will run faster?

that is the disadvantage of array formulas in more than 500 rows.
Try it with VBA:

Sub Test()
Dim LRow As Long
Dim i As Long
Dim rngC As Range
Dim firstAddress As String

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 2 To LRow
Set rngC = Sheets("Sheet2").Range("A1:A20000").Find _
(.Cells(i, 2), LookIn:=xlValues)
firstAddress = rngC.Address
Do
If rngC.Offset(0, 1) = .Cells(i, 3) Then
.Cells(i, 1) = rngC.Offset(0, 2)
End If
Set rngC = Sheets("Sheet2").Range("A1:A20000").FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address < firstAddress
Next
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Hi srm6

Working on what you posted, it looked like you wanted to invert the data!
If so, that post by the poster "Claus Busch" will not work. As that is just copy the values. But if that is what you are after, =Sheet2!C2



Kevin

This worked perfectly. We are all very happy with the results. Thank you so much for your time and effort on this matter. It is greatly appreciated.