How to look up for a value in a workbook in the network toanother workbook in Excel 2010?

The syntax of the VLOOKUP formula is:

=VLOOKUP(LookUpValue, LookupRange,Offset,LookUpType)

LookUp Value - The value that you want to find in the first column of LookupRange
LookUp Range - The data table in which you are searching
Offset - The column number from which you want to return data
LookUp Type - TRUE or FALSE, FALSE finds an exact match of LookUp Value; TRUE finds the closest match depending on sort order.

In your example the "Administracion" needs to change to the LookUp value for which you are finding details. The reference to Administracion would be in the Offset which would set the Offset to 8, column H being the 8th column of the data range starting in column A.

Thanks
Rob H

I should have also said, the VLOOKUP formula will look for only one value in the column and will return only one value from the specified column.

Going by the reference to rows 1 to 1000, it looks like you are trying to return the whole column.

Thanks
Rob H

elm9999,

Please make this more clear. Please correct me if I'm wrong.

From what I understand, you want to search Column H on Sheet Registros on File K:\Titular\Registro_Correos_Oficiales.xlsm. If the value sitting in Column H is Administracion, you want to copy the whole row onto a new sheet on a new file.

If that's the case, Vlookup is definitely not your answer.

I would say VBA is your best friend here. 2nd choice, using Index Match formula.

Can you please upload a sample file with dummy data for me to work on creating the VBA and Index Match sample for you?

Thank you very much guys,

I think is better idea to make a VBA code as Harry says, let me explain what I need to do.

Theere is a co-worker who receives a lot of e-mails everyday and she has to register them on a Table of the workbook and select wich department the e-mail belongs to.

Now, on the other hand, I need to make a new file for each department, so when people open the new file they can see the emails belongs to their department.

For example When Administration department opens the new file they can see only those records belong to Administration and also The Status = "Pendiene".

The main Workbook is on a network:

\\Myserver\Intercambio\Titular\Registro_Correos_Oficiales.xlsm or

K:\Titular\Registro_Correos_Oficiales.xlsm

Attached is the main file.

Thank you again in advance.
Registro-Correos-Oficiales2.xlsm

Thank you so much guys,

The option of hgholt works great and I'll use it to finish this job.

Regards,

elm9999,

Put the following code in a module in the personal macro workbook.

Open the daily file you referred to "K:\Titular\Registro_Correos_Oficiales.xlsm"

Then run the macro.

It will save a copy of the "K:\Titular\Registro_Correos_Oficiales.xlsm" in "K:\Tecnologias_informacion\" then remove all rows that H is not Administración.

This is kind of working the other around. Instead of searching for rows with H equal to Administración and copy to other workbook, this is making a copy of the workbook, and remove all rows that H is not equal to Administración.

Sub FilterListtoAdministracion()

Dim Datestamp As String, OrgFN As String, FPath As String, NewFFN As String, I As Integer
    
'Turn off screen update and auto calc to speed macro up
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Configure where to save new files
FPath = "K:\Tecnologias_informacion\"

'Create New Filename
OrgFN = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
Datestamp = Format(Now(), "MMDDYYYY-HHMM")
NewFFN = FPath & OrgFN & " " & Datestamp & ".xlsm"

'Save file with new path and stamped name
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        NewFFN, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True

'Remove all rows that H is not Administración
For I = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 2 Step -1
    If Cells(I, 8) <> "Administración" Then
        Range("A" & I).EntireRow.Delete
    End If
Next

'Reenable screen update and auto calc
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Select allOpen in new window

elm9999,

One big problem if using Index and Match, you will have to fill your new sheets with formulas which there are 2 big problems.

1) the file size is unnecessarily large
2) if you have lots of rows, the spreadsheet will run very slowly.

Using VBA does not increase your file size as much as formulas, and the result is a clean data without formulas for you to screw up in the future.