excel-training-courses

excel-training-courses - Lookups

Resolved · Low Priority · Version Standard

Lookups

Rebecca has attended:
Excel Advanced course

by - delegate Rebecca [14 posts] (2007 Oct 15 Mon, 16:17) Reply

I have one workbook with three worksheet within it, 2 with data and the third as a summary.

I would like to copy and paste lines from the two data sheets into the summary sheet that correlate. I would like to copy all the data from each corresponding line (which is 3 colums work of data) I believe that I have to combine the IF statement with ISERROR and VLOOKUP. I am trying to determine which lines are present in both of the two data worksheets

Can you please help me

many thanks

Becky

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 3 Wed	Bloomsbury	0	FULL
2003	2008 Dec 5 Fri	Bayswater	1	book now	£195	£205
2002/XP	2008 Dec 10 Wed	Tooting	0	FULL
2003	2008 Dec 10 Wed	Bloomsbury	0	FULL
2007	2008 Dec 10 Wed	Bloomsbury	2	book now	£240	£245
2003	2008 Dec 12 Fri	Southwark	5	book now	£219	£235
Full Schedule: See all 96 Excel Advanced course dates. Bookings currently available until 24th November 2009.

RE: Lookups

by - delegate Paul gold contributer [121 posts] (2007 Oct 18 Thu, 10:29) Reply

Hi Becky

I think you're nearly there. I would personally use something like

=IF(ISNA(VLOOKUP(B1,Sheet2!A:E,5,0),"",VLOOKUP(B1,Sheet2!A:E,5,0)))

this would return a blank if the figure you're looking for is not in the target table, otherwise display the resultant figure.

then from that, you can do a simple sort to make your sheet look nice and pretty :)

Hope this helps.
Any questions, feel free to email me - tealeaves18@hotmail.com

Paul

RE: Lookups

by - trainer Richard platinum contributer [818 posts] (2007 Oct 23 Tue, 10:09) Reply

Thanks Paul.

If the above solution has answered the question, please will you mark this question as resolved.

Many thanks

Richard

· Improve Performance Of Your Excel Macros
· Crazy Things People Have Done With Excel
· Microsoft Excel Training Advanced Courses - Making Data Validation Work For You
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· Database Tips from an Excel Training Course

Excel tip:

Formula for last day of month

In some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1

View all Excel hints and tips

Rate this page: