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

 

[parsec]

Hi Aladin
1: I have it in 9 cell that I need to retrieve the data from

2: I also retrieve data from 9 different cells

Thank you

 

[Aladin Akyurek]

...

1: I have it in 9 cell that I need to retrieve the data from

2: I also retrieve data from 9 different cells

...

I'm sorry I'm unable construct your 2 points as answers to the question I posed.

I don't see how you can have a performance problem if you just have 9 x 9 = 81 VLOOKUP formulas in your spreadsheet.

 

[just_jon]

Do you have any other formulas which may be slowing down performance?

Do you have any Event macros which might be kicking in?

 

[Smitty]

Can you post a shot of your sheet?

What's the current file size?

I also see drive "I" in your formula; I'm assuming that's a network drive. That could be the issue. If it is a network drive, try saving the workbook to your PC and just back up to the network.

I know next to nothing about networks, but one of our IS folks explained that working on a workbook over a network actually makes the server do the work, not your PC.

Smitty

 

[parsec]

Thank you for taking the time to help

here is what I have
9 columns with the formula in 6000 cells

thank you
John

 

[Aladin Akyurek]

Thank you for taking the time to help

here is what I have
9 columns with the formula in 6000 cells

thank you
John

Are you retrieving just from a single column, that is, from column 8 of your table?

What kind of data does the first column of the table house -- dates, text, or just numbers? Is the data sorted on the first column?

 

[parsec]

To pennysaver
No it is not a network drive just a simple disk partion that I have to house my work in a different area of the same HD

For Aladin


Are you retrieving just from a single column, that is, from column 8 of your table?:
No I retrieve data from 7 different columns

What kind of data does the first column of the table house -- dates, text, or just numbers? Is the data sorted on the first column?:

only #'s in this format 31471-6
no is not sorted

 

[Aladin Akyurek]

...Are you retrieving just from a single column, that is, from column 8 of your table?:
No I retrieve data from 7 different columns

What kind of data does the first column of the table house -- dates, text, or just numbers? Is the data sorted on the first column?:

only #'s in this format 31471-6
no is not sorted

If you can sort the table on its first column, you can make use of a faster lookup formula by setting the match-type to 1 (that's to TRUE).

You can also get some speed from the following set up:

Insert a column before your current range of lookup values. Also: Enter 7 numbers indicating "from which column the data must be retrieved" in row 1 from C1 on.

In A2 enter & copy down:

=MATCH(B2,'I:\NATURES MARKET WORK SPACE\WORKING IN\VENDORS CATALOGS\STOW\STOW CATALOG\[CHE Price List run on 3-3-04.xls]Sheet1'!$A$1:$A$5000,0)

B2 houses a lookup value of interest.

In C2 enter:

=IF(ISNUMBER($A2),INDEX('I:\NATURES MARKET WORK SPACE\WORKING IN\VENDORS CATALOGS\STOW\STOW CATALOG\[CHE Price List run on 3-3-04.xls]Sheet1'!$A$1:$M$5000,$A2,C$1),"")

where C1 houses a number indicating "from which column to retrieve". Copy this formula across for those 7 columns of interest, then down.

 

[parsec]

Thank you Aladin

John

 

[santeria]

I am trying to get a Network VLOOKUP to work.
It's really Puzzling because the Vlookup actually does Link ( it came up with a dialogue Box, "Which Tab do you want to Link with" or something similar), but the cells are pretty precise, the Names are in the manner:

Surname,Firstname

And I was hoping that I could get it to lookup based on the last name.

The Formula is :

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

And seems to come within a whisker of getting the data link.
I am not real clear if Vlookup needs a precise match or ...?
Maybe something else will get something close ??

Anyway, I am about one step off the link...
If I could get this functioning, my Handy Dandy Data Retriever could work

Kind of a Package retriever.
Or A Labrador Retriever ??

Anwyays, any advice appreciated.