98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nested vlookup with a Sum
Nested vlookup with a Sum
Resolved · Medium Priority · Version 2003
Tue 15 Jan 2013, 10:37Delegate Gerrard said...
Gerrard has attended:
Excel Advanced course
Excel VBA Intermediate course
Nested vlookup with a Sum
The sum I am trying to do is work out ADR. Which is total revenue divided by a number of rooms.
The challenge with my sum is this. I need to do two vlookups and add together two amounts of revenue. Which is this "=VLOOKUP(C32,Jan!$B$8:$AE$38,6,FALSE)+(VLOOKUP(C32,Jan!$B$8:$AH$38,27,FALSE))"
Then I need to add up two figures that gives me the total rooms sold. Which is just this "=SUM(C39+C36)"
and this is where it goes wrong, I cannot get the Total revenue (the vlookup) to divide by the total rooms sold.
I need it all in one formula and this one does not work. It is almost like it is a continuous calculation. When I place the formulas in two different cells, it works.
"=SUM(VLOOKUP(C32,Jan!$B$8:$AE$38,6,FALSE)+(VLOOKUP(C32,Jan!$B$8:$AH$38,27,FALSE))/(C39+C36))"
Any Idea's?
Tue 15 Jan 2013, 12:01Trainer Gary said...
RE: Nested vlookup with a Sum
Hi Gerrard
Good to hear from you, I hope the team are well.
I've had a play around with a mockup version here and I can't see a problem with it.
Would it be possible for you to send me a copy of the workbook so I can see what might be going on? My address is gary (at) stl-training.co.uk.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 15 Jan 2013, 16:48Trainer Gary said...
RE: Nested vlookup with a Sum
Hi Gerrard
Thanks for sending the file over. I had a look over the formulas and it seems that there were too many brackets in the cell. I've simplified it down to:
=(VLOOKUP(C32,Jan!$B$8:$AE$38,6,FALSE)+VLOOKUP(C32,Jan!$B$8:$AH$38,27,FALSE))/(C39+C36)
And that seems to work.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 22 Jan 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating charts in Excel 2010Here's how to present your data in a chart format: |
