# Vlookup Problem

#### clara200

##### New Member
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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Book1
LMNO
14
15
16
17PAZU123\$ 1,000
18
19
20
21
22PAZU123\$ 2,000
23
24
25
26
27
28PAZU123\$ 3,000
VAT

Sample Lookup Sheet:
Book1
HIJKLMN
1PAZU123100020003000
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...

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
ABCDEFGHIJKLMN
1SampleofSheet1
2
3AccountPeriodTransDateJournalBaseAmt(+/-)OtherAcqCostsPurchasesMajorPropertySalesSalesReferenceBaseAmt(+/-)Description
44000200601227/02/200612637-1,774.08-1,774.0814072-1,774.08Insurance2005/2006
54000200601201/03/200612637-2,953.13-2,953.1314075-2,953.13Rent25/3/06-23/6/06
64000200601201/03/200612637-3,083.63-3,083.6314076-3,083.63Rent25/3/06-23/6/06
74000200601201/03/200612637-7,997.50-7,997.5014077-7,997.50Rent25/3/06-23/6/06
84000200601202/03/200612631-568.75-568.75DUKIPF-231-568.75Rent25/3/06-23/6/06
94000200601202/03/200612631-17.50-17.50DUKIPF-231-17.50GrndRnt25/3/6-24/3/06
104000200601202/03/200612631-3,062.50-3,062.50DUKIPF-232-3,062.50Rent25/3/06-23/6/06
114000200601202/03/200612631-2,302.26-2,302.26DUKIPF-232-2,302.26Rent25/3/06-23/6/06
124000200601202/03/200612631-2,051.46-2,051.46DUKIPF-232-2,051.46Rent22/2/06-23/6/06
134000200601202/03/200612631-1,793.75-1,793.75DUKIPF-232-1,793.75Rent25/3/06-23/6/06
144000200601228/02/20061259887.5087.50PIND365787.505462M/8427-ELECTSRVS
154000200601228/02/2006126041,312.501,312.50PIND36631,312.501110civ/strucengininpt
164000200601228/02/200612606350.00350.00PIND3665350.002005256professionalfees
VAT

IVAT0207.xls
ABCDEFGHIJKL
1SampleofSheet2
2
3AccountPeriodTransactionDateJournalBaseAmt(+/-)TheoreticalVATActualVATDifferenceReferenceDescriptionJournalTypeJournalSource
4
50001200601201/03/200612637-211,925.00-37,086.88Same.0014081Rent25/3/06-23/6/06RENTAB
60001200601214/03/200612639-166,500.00-29,137.50Same.00DUKIPF-243Rent25/03/06-23/06/06RENTAB
70001200601208/03/200612619-152,500.00-26,687.50Same.00106009Rent25/3/06-23/6/06RENTAB
80001200601208/03/200612619-141,412.50-24,747.19Same.00106001Rent25/3/06-23/6/06RENTAB
90001200601202/03/200612631-113,750.00-19,906.25Same.00DUKIPF-234Rent1/4/06-30/4/06RENTAB
100001200601202/03/200612632-105,000.00-18,375.00Same.00DUKIPF-239Rent25/3/06-23/6/06RENTAB
110001200601208/03/200612619-94,668.75-16,567.03Same.00106010Rent25/3/06-23/6/06RENTAB
120001200601208/03/200612619-78,750.00-13,781.25Same.00105963Rent25/3/06-23/6/06RENTAB
130001200601208/03/200612619-76,250.00-13,343.75Same.00105952Rent25/3/06-23/6/06RENTAB
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.

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 ?

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.

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.

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?

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!

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 ?

Replies
3
Views
264
Replies
11
Views
1K
Replies
4
Views
747
Replies
3
Views
272
Replies
6
Views
820

1,202,987
Messages
6,052,936
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back