bank rec

[thedog29]

Hi folks, wonder if someone could help me with this one. i have two worksheets, one is the bank statements the other is my own record of cheques paid out etc.

basically what i want to do is get a cheque number from the statements look and find that cheque number in the cheques paid tab and return a value to a cell in the statement tab.

 

[GlennUK]

Look at the VLOOKUP function.

 

[VoG]

Hi. Try VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[thedog29]

Thanks folks, i'll give that a go

 

[thedog29]

I'm not having any luck with Vlookup. i think it may be that the values i'm looking at are not an exact match. i want to look at colum C check this against colum A in the cheques workings tab and return the EL/PL number in colum G of the statement tab. see below for example

Excel Workbook
	A	B	C	D	E	F	G
14	Booked date	Interest Date	Text	Amount	Balance
15	04/21/2010	04/21/2010	CH 205299	-23,027.44	-12,927.44		#VALUE!
16	04/21/2010	04/21/2010	tfr per a xxxxxxxxxxxxxx	23,027.44	10,100.00		#VALUE!
17	04/22/2010	04/22/2010	CH 205295	-9,900.00	200.00		#N/A
Statement @ 14-03-11

Excel 2003

Cell Formulas
Range	Formula
G15	=VLOOKUP(--C15,'cheque workings'!$A$6:'cheque workings'!$A$5136,3,1)
G16	=VLOOKUP(--C16,'cheque workings'!$A$6:'cheque workings'!$A$5136,3,1)
G17	=VLOOKUP(C17,'cheque workings'!$A$6:'cheque workings'!$A$5136,3,1)

Excel Workbook
	A	B	C
5	Cheque no	Date	EL/PL number
6	205398	06-May-09	EL 212
7	205399	06-May-09	EL 212
8	205400	06-May-09	EL 212
9	205801	06-May-09	EL 212
cheque workings

Excel 2003

 

[VoG]

Maybe

=VLOOKUP(RIGHT(C15,6)+0,'cheque workings'!$A$6:$C$5136,3,FALSE)

 

[thedog29]

Thanks VOG that has worked.

 

[thedog29]

right now that i got the first bit working i want to use vlookup again to look up a cheque number in colum c of the statement tab then return both the booked date and the amount to colums D & E in the cheque workings tab. can this be done?

Excel Workbook
	A	B	C	D
14	Booked date	Interest Date	Text	Amount
15	05/18/2010	05/18/2010	CH 205230	-894.00
16	05/12/2010	05/12/2010	CH 205253	-110,000.00
17	04/21/2010	04/21/2010	CH 205299	-23,027.44
18	04/22/2010	04/22/2010	CH 205295	-9,900.00
19	04/22/2010	04/22/2010	CH 205294	-40,000.00
20	04/26/2010	04/26/2010	CH 205298	-124,080.00
21	09/10/2010	09/10/2010	CH 205297	-9,583.33
Statement @ 14-03-11 (2)

Excel Workbook
	A	B	C	D	E
5	Cheque no	Date	EL/PL number	Date paid	Amount Paid
6	205398	06-May-09	EL 212
7	205399	06-May-09	EL 212
8	205400	06-May-09	EL 212
9	205801	06-May-09	EL 212
10	205802	06-May-09	EL 212
11	205803	06-May-09	EL 212
12	205804	06-May-09	EL 212
13	205805	06-May-09	EL 212
14	205806	06-May-09	EL 212
15	205807	06-May-09	EL 212
Excel 2003 cheque workings

Excel 2003

 

[VoG]

For the amount you can use VLOOKUP. For the date you'll need INDEX/MATCH http://www.contextures.com/xlFunctions03.html