I have a large vector (about 10 000 elements) and I want to make a lookup to
find a value and to put it into a grid.
My problem is that I have to make these lookups many times
(24h*60min*20=28800) into 10 000 elements vector
My data looks like this:

Station Time Stn_Time Vehicle
A 0:01 A_0:01 1
A 0:02 A_0:02 2
C 0:03 C_0:03 3
D 0:04 D_0:04 4
E 0:05 E_0:05 5
F 0:06 F_0:06 6

(tbl. 1)

i.e. for each STN and each minute I have a Vehicle. I want to present this
in a grid, where the left column contains stations and the header row
contains minutes, and in the grid inside - Vehicles:
0:01 0:02 0:03 0:04 0:05 0:06
A 1 2
B
C


(tbl.2)

To get this representation, I use VLookup function in the above grid:
=IF(ISNA(VLOOKUP($A2&"_"&B1,Sheet2!$C$2:$D$7,2,FAL SE)),"",VLOOKUP($A2&"_"&B1
,Sheet2!$C$2:$D$7,2,FALSE))
It works fine, except that it is extremely slow, because VLOOKUP function is
called around 28800 times and it looks every time into my array of 10000
elements. And it takes more than 7 minutes!!!

Is there any better way to acieve that simple task?
(I am sure it is, but I don't know it) :-(

Any suggestions are welcome

Yakimo

An array-entered VLOOKUP works just fine.

For my test, I created 10,000 randomly generated station, time, and
vehicle values in columns B, C, and E, starting with row 2. For the
Stn_Time column, in C2 I used the formula =B2&"_"&TEXT(C2,"hh:mm"). C2
was copied down to C3:C10001.

The 60 time values (00:01 to 01:00) I had in H11:BO11. The 10 station
values were in G13:G22.

Then, in H12 enter the formula =TEXT(H11,"hh:mm"). Copy H12 to
I12:BO12.

Now, select H13:BO22 and enter the *array* formula =VLOOKUP($G13:$G22
&"_"&H$12:BO$12,$D$2:$E$10001,2,FALSE)

Note the use of both absolute and relative references in the same cell
reference.

It takes under a second to recalculate on my computer when I change the
(random) data in B (or C or E).

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
s says...
I have a large vector (about 10 000 elements) and I want to make a lookup to
find a value and to put it into a grid.
My problem is that I have to make these lookups many times
(24h*60min*20=28800) into 10 000 elements vector
My data looks like this:

Station Time Stn_Time Vehicle
A 0:01 A_0:01 1
A 0:02 A_0:02 2
C 0:03 C_0:03 3
D 0:04 D_0:04 4
E 0:05 E_0:05 5
F 0:06 F_0:06 6

(tbl. 1)

i.e. for each STN and each minute I have a Vehicle. I want to present this
in a grid, where the left column contains stations and the header row
contains minutes, and in the grid inside - Vehicles:
0:01 0:02 0:03 0:04 0:05 0:06
A 1 2
B
C


(tbl.2)

To get this representation, I use VLookup function in the above grid:
=IF(ISNA(VLOOKUP($A2&"_"&B1,Sheet2!$C$2:$D$7,2,FAL SE)),"",VLOOKUP($A2&"_"&B1
,Sheet2!$C$2:$D$7,2,FALSE))
It works fine, except that it is extremely slow, because VLOOKUP function is
called around 28800 times and it looks every time into my array of 10000
elements. And it takes more than 7 minutes!!!

Is there any better way to acieve that simple task?
(I am sure it is, but I don't know it) :-(

Any suggestions are welcome

Yakimo