Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
LOOKUP function- excel
Hi,
I have a several workbook with monthly data recorded under each period ie P1, P2, P3 etc.
There is then a summary sheet which collates all of this data when the period is specified in a cell using the 'LOOKUP' function, so if 'P9' is written in cell B1 the formula returns P9 data.
The problem is that now we are in double digits ie P10 the formula is returning P1 data.
How can I amend the formula to return P10 data and in future P11 & P12?
Thanks
Raani
RE: LOOKUP function- excel
Hi Raani
Thanks for getting in touch. My guess is that the formula has been set for an approximate match, not an exact one.
To be certain, can you paste in the formula in a reply please in order I can verify it?
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: LOOKUP function- excel
Hi Gary,
Thanks, the formula is:
=LOOKUP(Totals!$B$1,'Y:\2013 Weekly Trading\WTR 2013\New Weekly Trading\[Insurance.xlsx]IT'!$C$25:$C$192,'Y:\2013 Weekly Trading\WTR 2013\New Weekly Trading\[Insurance.xlsx]IT'!$C$26:$C$192)
Raani
RE: LOOKUP function- excel
Hi Raani
Thanks for your reply. The LOOKUP function has been depreciated in newer versions of Excel as there are more efficient ways to achieve the same thing.
You can probably replace this formula with a VLOOKUP function in conjunction with a MATCH function, or possibly an INDEX and MATCH function.
Without seeing the files it's difficult to know which is the more appropriate solution. You're welcome to email them to gary@stl-training.co.uk if you wish.
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
