May 05 2020 02:48 AM
Hello all.
I am fairly new to Vlookup, and am looking for help with the following.
I have two separate excel spreadsheets. One includes data on amounts paid against all invoice numbers. The other is a Ledger that includes the original amount due for specific invoices that can be found in spreadsheet one.
In an additional column on my Ledger, I need to put the amounts received, from spreadsheet one, to said invoices on spreadsheet two.
Sheet one has over 45,000 rows. Sheet two, the Ledger, 700.
If anyone could advise, in simple step by step terms, how to do this, that would be great.
Many thanks A
May 05 2020 03:52 AM
Hi @han4har
Vlookup is the most helpful function for a dataset in excel but is a bit confusing for start using
So let me dived this is 2 parts sheet one and two
Sheet one - Amount Paid (Remittance)
Tip: The secret to VLOOKUP is to organize your data so that the value you looking up (invoice#) is to the left of the return value (Amount Paid) you want to find.
Column A = Invoice #
Column B = Amount Paid
Invoice # | Amount Paid |
1 | £ 10.00 |
2 | £ 12.00 |
3 | £ 13.00 |
4 | £ 15.00 |
5 | £ 18.00 |
6 | £ 19.00 |
7 | £ 20.00 |
8 | £ 16.00 |
9 | £ 16.00 |
10 | £ 14.00 |
11 | £ 16.00 |
12 | £ 16.00 |
13 | £ 13.00 |
14 | £ 14.00 |
15 | £ 19.00 |
16 | £ 17.00 |
17 | £ 16.00 |
18 | £ 13.00 |
19 | £ 19.00 |
Sheet two - Ledger
Column A = Invoice #
Column B = Amount Billed
Invoice # | Amount Billed |
1 | £ 10.00 |
2 | £ 12.50 |
3 | £ 13.00 |
4 | £ 14.00 |
5 | £ 18.00 |
6 | £ 21.00 |
7 | £ 20.00 |
8 | £ 16.00 |
9 | £ 15.00 |
10 | £ 14.00 |
11 | £ 16.00 |
12 | £ 16.00 |
13 | £ 14.00 |
14 | £ 21.00 |
15 | £ 19.00 |
16 | £ 17.00 |
17 | £ 15.00 |
18 | £ 15.35 |
19 | £ 17.50 |
So now the formula explained:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_value = What you want to look up
table_array = Where you want to look for it
col_index_num = The column number in the range containing the value to return
range_lookup = Return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE
Now is the time to look for the value on sheet one that matches invoice number on sheet number two
So in your case at sheet two (Ledger) on cell C2 you will have
=VLOOKUP(A2,Remittance!A1:B20,2,0)
Explanation:
A2 = This is the location of the Invoice # you want to look up
Remittance!A1:B20 = This is where all the info is. In this case at the tab Remittance! from cell A1 to B20.
2 = this is the column number that you have the amount paid at the array (column A is number 1, column B is number 2 and so on)
0 = here once you want an exact match leave this as a False or 0, you don't want an approximate match
Check the attached file with this example
Hope this helps you
This explanation was based on the VLOOKUP function at:
https://support.office.com/en-gb/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1