LOOKUP function- excel

Resolved · Urgent Priority · Version 2010

Thu 3 Oct 2013, 11:03 Delegate Raani said...

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

Thu 3 Oct 2013, 11:14 Trainer Gary said...

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

Thu 3 Oct 2013, 11:28 Delegate Raani said...

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

Thu 3 Oct 2013, 11:56 Trainer Gary said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Create a hyperlink navigation sheet

In large files, it is often useful to have a front sheet with hyperlinks to the key databases and summary calculations in your spreadsheet. Hyperlinks can save you and (more importantly) those less familiar with your spreadsheet a great deal of pointless scrolling between and within sheets.

Hyperlinks appear as underlined text and can jump to any cell or range name in your file. You can also use hyperlinks to jump to other files.

To create a hyperlink to a location in the active workbook: (1) Select the cell that contains the text you want to use as the hyperlink and choose Insert|Hyperlink.(2)Click Place in this document.(3)Choose the sheet you want to link to or the range name from the list of "Defined Names".(4)If necessary, type the cell reference in the Type in the cell reference box. (5) Click OK.

View all Excel hints and tips