Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Vlookup formula

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Vlookup formula

Resolved · Medium Priority · Version 2010

Reply Thu 12 Jan 2012, 10:53Delegate Chris said...

Chris has attended:
Excel Advanced course

Vlookup

Im currently using the below formula to look a list of numbers in another list of numbers and if found, output the minutes dialled for that number.

Its looking for the numbers in column G in column A, and if it finds them outputting the minutes dialled which are in column D

=IF(ISERROR(VLOOKUP(G13,$A$2:$D$386,1,FALSE)),0,VLOOKUP(G13,$A$2:$D$386,4,FALSE))

The problem I have is that for the numbers that are in column G, here are more than one entry in Column A, and the lookup is only finding the first entry.

How can I edit this formula to get it to find all instances of each number in column A and sum all of the related entries in column D?

For upcoming training course dates see: Pricing & availability

Reply Thu 12 Jan 2012, 13:13Trainer Rodney said...

RE: Vlookup

Hello Chris,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding the use of Vlookup.

Send me your file which contains only the data you are referring to and I will find the best solution to this problem. Unfortunately, Vlookup will always find the first item you are looking for so I will find a workaround which solve this issue.

My email is: rl

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Reply Thu 12 Jan 2012, 13:22Delegate Chris said...

RE: Vlookup

Hi Rodney,

Thanks for your reply.

I think ive found a solution. If I pivot the data first to get the totals and then run the lookup it should work.

Fingers crossed!

Kind regards,
Chris

Reply Fri 13 Jan 2012, 12:41Trainer Rodney said...

RE: Vlookup

Hello Chris,

Did you have any joy with your proposed solution?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Reply Fri 13 Jan 2012, 13:28Delegate Chris said...

RE: Vlookup

Hi Rodney,

Yes it seems to have worked :)

Regards,
Chris

Excel articles

· Why Excel Has More Cloud Coverage
· How To Insert, Delete, Format, Hide, Copy And Protect Worksheets In Excel
· The Pursuit of Excellence
· VBA Excel 2007
· Interesting Lifestyle Applications for Microsoft Excel

Please browse our web site to find out more about
microsoft.excel.training and other Microsoft training courses.

Excel tip:

Entering text in Multiple Worksheets

If you have a number of worksheets in a workbook that require the same information (data or tables) on each worksheet, this can be done as follows:

Hold down the Ctrl key and click one or more of the additional worksheet tabs (i.e. Sheet2, Sheet3, etc).

In your mainsheet (Sheet1) enter the required data or design a table.

When done 'Click' on the other sheet tabs and you will see that the information entered in Sheet1 is on all the other selected worksheets.

NB Do not forget to deselect the worksheets - otherwise you may add data to the main worksheet and all the selected worksheets will also have that data!

View all Excel hints and tips