VLookup 2 Colums 2 spreadsheets

**beaker** · 02-26-2008, 11:47 AM

hi didnt know if anyone would be able to help am trying to combine 2 spreadsheets Discounts and turnover . and using Vlookup To do this

Spreadsheet 1

A B C D E

A000001 blah 01150 15% ****
A000001 blah 01121 20% ****
A000001 blah 01130 50% ****
A000002 Fred 01115 15% ****
A000002 Fred 01120 20% ****

Spreadsheet 2

A B C D

A000001 blah 01150 £1500
A000001 blah 01121 £2500
A000001 blah 02222 £1234
A000001 blah 01130 £50
A000002 Fred 01115 £150
A000002 Fred 01120 £45

so i would be looking to replace the Stars with the Turnover. the Common identifiers would be Account number "A00001" etc and Nominal Class "01150" etc

has anyone got any ideas as been racking brains for this

cheers

j

**Conor** · 02-26-2008, 12:07 PM

What is the "Turnover"? Is it the price in sterling? Will there always exist a match on both account number and nominal class? Do nominal classes repeat in multiple account numbers or are they unique to a single account number?

**WinteE** · 02-26-2008, 02:09 PM

Hi Beaker,

As the turnover is an amount I suppose you could use SUMPRODUCT(). You'll find an example of this function on my site. Otherwise, if you don't succeed using SUMPRODUCT() you can try the MLOOKUP() user defined function that's also to be found on my site.

Erik

**beaker** · 02-27-2008, 04:49 AM

hi Will look at site now

yep the nominal classes repeat for the account numbers and yeah turnover in stirling

is this a simple fix and am being V blonde lol

beaker

**DannyG** · 02-27-2008, 05:34 AM

Hey,

Just a suggestion as I have not tried this out but could you not create a unique ID field in each of the spreadsheets which is made up of the account number and the other code (i.e. use CONCATENATE formula to create a field that links them together in one field).

Then you can use the VLOOKUP formula as normal based on this unique ID and return the turnover value you are looking for.

Thanks