Excel Help please

[sprintmp]

Good morning

Not being an Excel expert, I need some help please.

I have the following formula in a excel worksheet. The workbook contains multiple sheets (Master, December 2005, January 2006 etc). The formula is in the Master sheet.

=IF(ISERROR(LOOKUP($A5,'December 2005'!$A:$A,'December 2005'!$C:$C)),0,LOOKUP($A5,'December 2005'!$A:$A,'December 2005'!$C:$C))

Basicly it looks up a value from another sheet (December 2005). What I would like to do is have the sheet reference ('December 2005') in a cell and look up using that cell value.

For example if in the Master sheet cell A1 contains 'December 2005', the formula could look like this:

=IF(ISERROR(LOOKUP($A5,A1!$A:$A,A1!$C:$C)),0,LOOKUP($A5,,A1!$A:$A,A1!$C:$C))

However this dosn't work. Can anyone suggest a solution?

Also when using LOOKUP, if the value is not found, it returns the value closest to it. Is there a way of returning a zero if the value is not found?

Thanks in advance....

P

Edited by sprintmp on Saturday 1st July 09:32

[Platinum]

You could substitute your LOOKUP formula for VLOOKUP. (See below) this will look for an exact match & return the adjacent value (if the box containing text to lookup was 'a5'):

=VLOOKUP(a5,a1:b3,2,false)

Where the lookup value is A5, A1:B3 is the range, 2 is the column number from which the value is returned, and false denotes that it has to be an exact match.

A B
1 june £5
2 may £6
3 july £3
4
5 may

You could also use INDEX & MATCH: =INDEX(a1:b3,MATCH(a5,a1:a3,0),2) Where a1:b3 is the range, a5 is the lookup value, a1:a3 is the lookup range, 0 denotes an exact match & 2 is the column number from which to return the value.

Presumably your IF & ISERROR parts return 0?

As for the sheet reference, I will have to try and get my head round it.

Check out help on Excel as I have found it to be very helpful and it may explain thing better than I have.

Edited by Platinum on Saturday 1st July 11:09

[atomicrex]

OK have a play with this

=IF(ISERROR(INDEX(INDIRECT("'"&A3&"'!$A:$C",MATCH(A5,INDIRECT("'"&A3&"'!$A:$A",0),3)),0,INDEX(INDIRECT("'"&A3&"'!$A:$C",MATCH(A5,INDIRECT("'"&A3&"'!$A:$A",0),3))

Breaking it down I have used the index and match functions as personally I think that VLookup needs to be put in the bin for wasting resources!!!

The indirect will allow you to use a concatenated string as a reference. You can build the string as I have or use the concatenate function.

The 0 at the end of the match will make sure it gives you an exact match and not the closest!

The 3 at the end of the index is the column you wish to return.

I have a spread sheet saved with a mock up of this that I can email if you need further clarification

Jon

[atomicrex]

Oh god!

Please substitute the for the relevant bits!!!

Also Cell A3 is on the Master Sheet and is formated to text. It then contains the name of the sheet you want to use....December 2005

This then concatenates the string to make 'December 2005'!$A:$A

You can then do what you like!

I like big formulae

Edited by atomicrex on Sunday 2nd July 08:19

[hornet]

From the top of my head, you can use Insert/Name/Define to give the whole sheet (well, the lookup range on that sheet) a simpler name. Ages since I did that though, so a bit rusty.

Edit: I think you could then put a reference to that range on your master sheet, so for the purpose of the VLOOKUP you just point to that reference.

Edited by hornet on Sunday 2nd July 16:16

[sprintmp]

Thanks for the suggestions. I'll try them over the next couple of days - when I get time between decorating
P

[AtomicRex]

hornet said:

From the top of my head, you can use Insert/Name/Define to give the whole sheet (well, the lookup range on that sheet) a simpler name. Ages since I did that though, so a bit rusty.

Edit: I think you could then put a reference to that range on your master sheet, so for the purpose of the VLOOKUP you just point to that reference.

Edited by hornet on Sunday 2nd July 16:16

Ahhhhhh

Get away from using these VLookUp's.....they are the bain of my life, and eat memory and resources faster than a fat tarts arse can eat a thong!

[sprintmp]

Hi Jon,

Thanks for the formula - it works!

Thanks for your help.

P

Edited by sprintmp on Tuesday 4th July 14:23