Vlookup

[han4har]

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

[#2]

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