Compare fields in Excel?

[GB]

Hello again,

I asked this question a few weeks ago and received a decent solution.

The spreadsheet I have is very long so the problem with this solution
is when saving the spreadsheet it's extremely slow (takes about 10
mins to save). The previous solution is shown below.

=SUMPRODUCT((Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1)*(Sheet2!$C$1:$C$10=C1)*Sheet2!$D$1:$D$10)

I'm hoping somebody can assist in getting a different solution that
will not make the sheet slower. My proposal is shown below.

My main spreadsheet looks like shown below. What I need is to have
column D filled automatically based on the values from Column A, B,
and C. To determine what value should be in D I have a second work
sheet which has all possible combinations (about 600) with the values
of D.
I want my main sheet to compare the values of A, B, and C with the
values of A, B, and C on the second sheet. If there's a match the
value of D on my second sheet should be put into D on my main sheet



Column A ColumnB ColumnC Column D
Sydney Los Angeles Express 3
Sydney Los Angeles Deferred 5
Sydney Dallas Deferred 5
Hong Kong New York Express 1
Taipei San Francisco Express 2
Taipei Los Angeles Deferred 4
Tokyo Los Angeles Express 2
Manila Dallas Express 1


Thanks...

 

[Alan Beban]

I have no idea whether it will speed things up or slow them down-- I
leave that to you -- but consider the following:

On Sheet2 enter in E1 =A1&B1&C1 and fill down to the end of your data.
Then Copy/PasteSpecial-Values Column E to eliminate the formulas. Then
copy Column D to Column F.

Then in Cell D1 of Sheet1 enter =VLOOKUP(A1&B1&C1,Sheet7!E1:F5,2,FALSE)
and fill down as far as your data goes.

Alan Beban

 

[GB]

Thanks again Alan. This seems to be working much smoother

Is there a way to lock the range for sheet2 (E1:Fxxx) in the formula?.
When I fill down D1 of sheet 1 the range changes as it goes down,
which of course it needs to do for A1&B1&C1, but not for
Sheet7!E1:Fxxx.

Regards,
Mats

 

[Alan Beban]

Of course; I should have provided that in the first place. Use
E$1:F$xxxx (or a named range) instead of E1:Fxxxx. And I'm sure you
noted that the Sheet7! should have been Sheet2! (In my test file I was
using Sheet6 and Sheet7).

Alan Beban

 

[GB]

Thanks again. I appreciate all your help.

Rgds,