VLOOKUP vs INDEX/MATCH?

[parsec]

Hello to all
I would like to here your opinion about the VLOOKUP vs INDEX/MATCH function.
Is one faster than the other, what is the difference, and what it will be the criteria to use one over the other?

I have a sheet with several VLOOKUP formulas, it takes a long time to get updated also when I save it takes a long time what causes this, any way to remedy this?

Thank you
All
John

 

[Brian from Maui]

=VLOOKUP(A31,'\\tlhfw01\Benefit Management\Quality\Production Quality\[Production April Call Quality.xls]Monthly'!$A$1:$Z$82,1,0)

Why are you returning the 1st column?

 

[santeria]

Just Test Reasons at the moment...
I have tried 1 through 22 to see if I get a return... but not clear why the name is not being picked up.

Basically I want the Name, in Column B, and then a Piece of data from Column 22.

Kind of neat if I can get this to work.
It indexes fine on a local drive, but the files most people use are network drive files, and if I can get the access to work without opening the files, then it may be helpful... to me and others...

ray: ray:

 

[Brian from Maui]

I just tried it and it works on a network. Something like,

=VLOOKUP(B1,'J:\[Master.xls]NEW SAT'!$A$39:$B$49,2,0)

where J is the drive that's mapped on my computer. Try opening a new workbook and the workbook you'd like to use. You may have to use Windows Explorer and map the path to the workbook. Then enter Vlookup(lookup cell,then switch to the other workbook and click and drag the table, finish the syntax.

 

[santeria]

I guess my question is, does this form of Lookup depend on a relative address ( a Letter then Address of the File), or can a network address be used ?

Also, if the Vlookup has part of the name put in, and the cell referenced on certain files have a combined lastname,firstname structure, can the vlookup deal with that, or is this something more akin to MATCH/-INDEX ?

I have tried a number of variations, and it is not clear what Excel is "expecting". The silly thing works from the C drive during tests, but if I add in the full address my PC is setup for, then thing "fails"... just has NA error.

Would be so neat if it worked.

Any suggestions appreciated.

Ta

 

[santeria]

One of a few things perplexing me is that if I have a file Locally, the vlookup works, but if I look to the Network address, the lookup fails...




=VLOOKUP(A24,'C:\Temp\[ID Master.xls]Master'!$A$1:$M$80,1,0)

=VLOOKUP(A25,'E:\hroutsourcing\benefits\IDs\[ID Master.xls]Master'!$A$1:$M$80,1,0)

However, if I file link in the way:

='F:\Quality\Production Quality\[Production April Call Quality.xls]Monthly'!$B$7

The right data is pulled, and works fine... so the connection seems viable.

The file the is local, will provide data when closed.
Does a file HAVE to be open to provide data?

Also, if the Data pulled from a cell link works, does the data stay "live", or is the data static from the moment it was linked and requires refreshing ?


Ta

 

[MDuff]

santeria,

I had the same exact issue you are having with network drives and lookups.

http://www.mrexcel.com/board2/viewtopic.php?t=71050&postdays=0&postorder=asc&start=10

the only way I found around it was to use a macro to open the sheet and then close it to refresh the formula if any one has any other ideas I'm open to them

Here is the Code

Private Sub Workbook_Open()
' if your sheet has a password
ActiveSheet.Unprotect Password:="XXXX"

Workbooks.Open Filename:= _
" '\\your network path here
ActiveWindow.Close False
Windows('"file name here").Activate

' if your sheet has a password
ActiveSheet.Protect Password:="XXXX", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[santeria]

So is that totally the only way around it ?

I am intrigued because I can get vlookup to check files on my local Hard drive, but it fails when the exact same file is on a network.

I am actually hoping to avoid the opening of the files.
Kind of defeats the purpose of the lookup if I have to open the file

I had thought about a mirror set of files to bypass the issue, but then that defeats the purpose of the network.

I am looking at Match and Index.

I can get so far, and then the Vlookup falls flat.

The basic collection of lookups I am hoping to set up is an aim to minimise RAM use on PCs, and maximise available data for staff supervisors and Managers.

Kind of seemed a logical way to go

Seems to be turning out that it may not be a practical way to go


Ah well, back to the drawing board I guess... Unless a Magician can fix it :wink:

 

[MDuff]

Ok if you or any one else finds an other solution please let me know
thanks

 

[santeria]

I think I may have found a way

 

[MDuff]

Please let me know ray: