Hi,
I am trying to get from this:
=IF(ISERROR(VLOOKUP(A3,'04-05'!A:C,3,0)=TRUE),0,VLOOKUP(A3,'04-05'!A:C,3,0))
..to this:
=(IF(ISERROR(VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!"&A:C),3,0)=TRUE),0,VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!"&A:C),3,0)))
but I am getting a zero result.
$D1 = Wed 05-May-10
Sheet name = 04-05
Any idea's where I am going wrong?
VLOOKUP Formula
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
VLOOKUP Formula
![Wales :wales:](./images/smilies/Wales.gif)
![UK :uk:](./images/smilies/UK.gif)
There's no place like home.....
-
- Administrator
- Posts: 78796
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78796
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP Formula
Apart from that, you have placed the quotes incorrectly. The entire argument to INDIRECT should be a string. The formula should be
=(IF(ISERROR(VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!A:C"),3,FALSE)=TRUE),0,VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!A:C"),3,FALSE)))
=(IF(ISERROR(VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!A:C"),3,FALSE)=TRUE),0,VLOOKUP(A3,INDIRECT("'"&TEXT(D$1,"dd-mm")&"'!A:C"),3,FALSE)))
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: VLOOKUP Formula
Good catch, but that was just a typo.
Thanks, works great!
Thanks, works great!
![Wales :wales:](./images/smilies/Wales.gif)
![UK :uk:](./images/smilies/UK.gif)
There's no place like home.....
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: VLOOKUP Formula
=sumif((Right(A2:A298,3),"F E",D2:D298))
Any idea what is wrong with this? Can 'Right' be used with Sum If ??
Any idea what is wrong with this? Can 'Right' be used with Sum If ??
![Wales :wales:](./images/smilies/Wales.gif)
![UK :uk:](./images/smilies/UK.gif)
There's no place like home.....
-
- Administrator
- Posts: 78796
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP Formula
The first and third arguments of SUMIF must be ranges, not formulas. But you can use wildcards in the second argument, so try
=SUMIF(A2:A298,"*F E",D2:D298)
=SUMIF(A2:A298,"*F E",D2:D298)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: VLOOKUP Formula
Thanks, that works. However, how would I adapt that for "mid"? I need to sum those that end in "F E" and those that have "F E" as the 21-23rd characters. (Separate calculations)
![Wales :wales:](./images/smilies/Wales.gif)
![UK :uk:](./images/smilies/UK.gif)
There's no place like home.....
-
- Administrator
- Posts: 78796
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP Formula
For values that contain "F E" (in any position), you'd use "*F E*" as second argument.
If you specifically want to look for "F E" in positions 21-23, you could use "????????????????????F E*" (that's 20 question marks)
If you specifically want to look for "F E" in positions 21-23, you could use "????????????????????F E*" (that's 20 question marks)
Best wishes,
Hans
Hans