Lookup many values quickly

[ksaw]

hello,

I am working with several very large files that are taking far too long to populate/save (nearly 20 minutes to save one file). The obvious reason for this is that I am using a substancial number of vlookups, and I am looking for perhaps a better alternative.

What I have is a sheet that will have transactions by store,by day, by type, by hour, and I am pulling them into a schedule. Currently, I have several thousand vlookup formulas that first concatenate the perspective cells, then vlookup to Data sheet!.

The data sheet is only 2 columns, column A has the concatenated title, and column B has the value I need.

Is there a faster way to do this? better yet, is there a way to do this from a linked worksheet (values will change monthly)?




FYI I am working in Excel 2007

 

[Aladin Akyurek]

hello,

I am working with several very large files that are taking far too long to populate/save (nearly 20 minutes to save one file). The obvious reason for this is that I am using a substancial number of vlookups, and I am looking for perhaps a better alternative.

What I have is a sheet that will have transactions by store,by day, by type, by hour, and I am pulling them into a schedule. Currently, I have several thousand vlookup formulas that first concatenate the perspective cells, then vlookup to Data sheet!.

The data sheet is only 2 columns, column A has the concatenated title, and column B has the value I need.

Is there a faster way to do this? better yet, is there a way to do this from a linked worksheet (values will change monthly)?




FYI I am working in Excel 2007

Is A:B sorted on A, the match range? If so, what is the current VLOOKUP fomula you are invoking?

 

[ksaw]

Yes it is..currently this is a sample of the vlookup. But, the concatenated cells change with each lookup formula (H1 changes each sheet, B1 changes each day of the week, C5 changes each row, and D9 changes each column)



VLOOKUP(CONCATENATE($H$1,$B$1,$C5,D$9),Data!$A:$B,2,FALSE)

 

[shg]

How about,

=IF(LOOKUP($H$1&$B$1&$C5&D$9, Data!$A:$A) = $H$1&$B$1&$C5&D$9, 
    LOOKUP($H$1&$B$1&$C5&D$9, Data!$A:$B), NA())

Two binary searches should be much faster than one linear search if you have a lot of data.

 

[Aladin Akyurek]

Yes it is..currently this is a sample of the vlookup. But, the concatenated cells change with each lookup formula (H1 changes each sheet, B1 changes each day of the week, C5 changes each row, and D9 changes each column)



VLOOKUP(CONCATENATE($H$1,$B$1,$C5,D$9),Data!$A:$B,2,FALSE)

Try the following set up...

=IF(LOOKUP($H$1&$B$1&$C5&D$9,Data!$A:$A)=$H$1&$B$1&$C5&D$9,
    LOOKUP($H$1&$B$1&$C5&D$9,Data!$A:$A,Data!$B:$B),"")

Would you report back how the performance is affected?