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?
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
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
