Vlookup Problem

[clara200]

Hey everyone,

I am having a few problems with a Vlookup formula. I am using the formula for the purpose of matching off a value eg “PAZU123” with what that value corresponds to in another sheet.

For instance if Sheet 1 has the value “PAZU123” and the value $1000 in the column beside it
that where PAZU123 occurs in Sheet 2 that it will place $1000 in the column beside it.

At the moment I am using the formula: =VLOOKUP(I1,VAT!$L$1:$M$5000,2,FALSE)
It works fine if the value eg PAZU001 occurs only once. The problem arises when the value eg PAZU001 occurs more than once. In other words PAZU001 occurs three or four times each time with a different amount eg $1000, $2678 etc. When this happens it gives me only the first value it finds for the three or four times PAZU001 occurs on Sheet 2. I was wondering was there a way Vlookup would actually give me the correct amounts on Sheet 2 relative to the amounts on sheet 1.

Below is a formula I made up to tell me when the error occurs every time a value occurs more than once eg PAZU001 that the word same will appear in the cell instead of the value on Sheet 1.

=IF(I2=I1,"SAME",VLOOKUP(I186,VAT!$L$1:$M$5000,2,FALSE))

Is there anyway around this problem apart from me manually entering the duplicate values myself. I would greatly appreciate any help going.

Thanks,
Seán.

 

[NBVC]

Sample for your VAT sheet:

Book1
	L	M	N	O
14
15
16
17	PAZU123	$ 1,000
18
19
20
21
22	PAZU123	$ 2,000
23
24
25
26
27
28	PAZU123	$ 3,000
VAT

Sample Lookup Sheet:

Book1
	H	I	J	K	L	M	N
1		PAZU123	1000	2000	3000
2
3
4
Sheet1

Formula in J2: =IF(COLUMNS($A:A)>COUNTIF(VAT!$L$1:$L$5000,$I1),"",INDEX(VAT!$M$1:$M$5000,SMALL(IF(VAT!$L$1:$L$5000=$I1,ROW(VAT!$L$1:$L$5000)),COLUMNS($A:A))))

confirmed with CTRL+SHIFT+ENTER, not just ENTER.

copy it to the right as far as you think you will need. You can then copy the formula down if you have to match entries from I2, I3, etc...

 

[clara200]

Thanks NBVC for your reply. I tried to use your formula and manipulate it for what I am doing but either I wasn't able to or it just wasn't suited for what I wsnted it to do. The diagrams below of my sample data should make it clearer what exactly I am trying to do.

Sample Data:

IVAT0207.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SampleofSheet1
2
3	Account	Period	TransDate	Journal	BaseAmt(+/-)	Other	AcqCosts	Purchases	Major	PropertySales	Sales	Reference	BaseAmt(+/-)	Description
4	4000	2006012	27/02/2006	12637	-1,774.08						-1,774.08	14072	-1,774.08	Insurance2005/2006
5	4000	2006012	01/03/2006	12637	-2,953.13						-2,953.13	14075	-2,953.13	Rent25/3/06-23/6/06
6	4000	2006012	01/03/2006	12637	-3,083.63						-3,083.63	14076	-3,083.63	Rent25/3/06-23/6/06
7	4000	2006012	01/03/2006	12637	-7,997.50						-7,997.50	14077	-7,997.50	Rent25/3/06-23/6/06
8	4000	2006012	02/03/2006	12631	-568.75						-568.75	DUKIPF-231	-568.75	Rent25/3/06-23/6/06
9	4000	2006012	02/03/2006	12631	-17.50						-17.50	DUKIPF-231	-17.50	GrndRnt25/3/6-24/3/06
10	4000	2006012	02/03/2006	12631	-3,062.50						-3,062.50	DUKIPF-232	-3,062.50	Rent25/3/06-23/6/06
11	4000	2006012	02/03/2006	12631	-2,302.26						-2,302.26	DUKIPF-232	-2,302.26	Rent25/3/06-23/6/06
12	4000	2006012	02/03/2006	12631	-2,051.46						-2,051.46	DUKIPF-232	-2,051.46	Rent22/2/06-23/6/06
13	4000	2006012	02/03/2006	12631	-1,793.75						-1,793.75	DUKIPF-232	-1,793.75	Rent25/3/06-23/6/06
14	4000	2006012	28/02/2006	12598	87.50			87.50				PIND3657	87.50	5462M/8427-ELECTSRVS
15	4000	2006012	28/02/2006	12604	1,312.50				1,312.50			PIND3663	1,312.50	1110civ/strucengininpt
16	4000	2006012	28/02/2006	12606	350.00				350.00			PIND3665	350.00	2005256professionalfees
VAT

 

[clara200]

IVAT0207.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	SampleofSheet2
2
3	Account	Period	TransactionDate	Journal	BaseAmt(+/-)	TheoreticalVAT	ActualVAT	Difference	Reference	Description	JournalType	JournalSource
4
5	0001	2006012	01/03/2006	12637	-211,925.00	-37,086.88	Same	.00	14081	Rent25/3/06-23/6/06	RENT	AB
6	0001	2006012	14/03/2006	12639	-166,500.00	-29,137.50	Same	.00	DUKIPF-243	Rent25/03/06-23/06/06	RENT	AB
7	0001	2006012	08/03/2006	12619	-152,500.00	-26,687.50	Same	.00	106009	Rent25/3/06-23/6/06	RENT	AB
8	0001	2006012	08/03/2006	12619	-141,412.50	-24,747.19	Same	.00	106001	Rent25/3/06-23/6/06	RENT	AB
9	0001	2006012	02/03/2006	12631	-113,750.00	-19,906.25	Same	.00	DUKIPF-234	Rent1/4/06-30/4/06	RENT	AB
10	0001	2006012	02/03/2006	12632	-105,000.00	-18,375.00	Same	.00	DUKIPF-239	Rent25/3/06-23/6/06	RENT	AB
11	0001	2006012	08/03/2006	12619	-94,668.75	-16,567.03	Same	.00	106010	Rent25/3/06-23/6/06	RENT	AB
12	0001	2006012	08/03/2006	12619	-78,750.00	-13,781.25	Same	.00	105963	Rent25/3/06-23/6/06	RENT	AB
13	0001	2006012	08/03/2006	12619	-76,250.00	-13,343.75	Same	.00	105952	Rent25/3/06-23/6/06	RENT	AB
Net Invoiced Sales

What I was hoping to do was (In column G Sheet2) transfer what the VAT on a specific sale from the Vat sheet to the sale it is associated with on Sheet 2 (Using the Sale Reference eg DUKIPF-234). What the VAT should be and what the Vat actually is differs sometimes so I have to do this every month.

Where I am running into difficulty is when the same sale reference is used a number of times as the VAT associated with the sale is divided up into different segments. The formula I am using does not allow for this. It will return the first value of Vat it finds for the sale reference rather than the correct one.

It would be great if I didn't have to do this manually as this error occurs quite often admist 1000's of transactions.

Once again thanking you all in advance,
Seán.

 

[NBVC]

So are you actually looking for the Sum of all VAT's listed under the duplicated References...so that you get one number for each Reference?


Maybe you need something like:

=SUMIF(VAT!$L$1:$L$5000,I1,VAT!$M$1:$M$5000) copied down ?

 

[clara200]

What I am looking for is to match off the amount for each duplicate reference rather than the total. I have the total already I just need to match each sale off with the vat allocated on it. The overall sale amount is fine but the sale can be made up of different amounts/sections (Usually a sale is made up of one individual sale but sometimes there can be a few sales in the one in other words they have the the same reference number) I just want to match them off with each other the problem is when they have the same reference it will just return me the first amount it finds for each one rather than the right one.

Thanks,
Seán.

 

[patrickmuldoon99]

You can try and generate a unique hash number so as to create a unique identifier to perform your lookup against. This will enable you to find the value within a set of non-unique entries.

 

[clara200]

thanks for your reply,
I hate to show my ignorance but when you say "generate a unique hash number" what do you mean? Or how might I go about it?

 

[patrickmuldoon99]

I don't really want to get into the complexities of the problem, i want to simply offer you an approach you may not have used before.

VLookup will return the first entry that it matches against, and there are several entries in your example that may govern one transaction that is charged VAT on.

So your problem is that you need to identify the correct element of the transaction that matches against the criteria you are setting. To this end it becomes obvious that you must search for a purely unique entry in the big long list you are doing a vlookup against. Using concatenation (e.g. a & b) you should be able to use a variable in that list to generate a unique key that you can then run your VLookup against

This approach may shed light on your problem, or it might be inapprpriate, but its an angle nonetheless!

 

[NBVC]

What I am looking for is to match off the amount for each duplicate reference rather than the total. I have the total already I just need to match each sale off with the vat allocated on it. The overall sale amount is fine but the sale can be made up of different amounts/sections (Usually a sale is made up of one individual sale but sometimes there can be a few sales in the one in other words they have the the same reference number) I just want to match them off with each other the problem is when they have the same reference it will just return me the first amount it finds for each one rather than the right one.

Thanks,
Seán.

I don't get why my original offering won't suit then? It pulls all the matches.

By the way, your sample doesn't really show an example of what we're looking at. Your second sheet doesn't show any of the duplicates from your first sheet and how you wish them to be handled.

Perhaps you can reshow your sample with appropriate data.....or perhaps I am just misinterpreting the whole thing ?