VLOOKUP Formula

[VegasNath]

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?

[HansV]

5 May is 05-05, not 04-05!

[HansV]

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

[VegasNath]

Good catch, but that was just a typo.

Thanks, works great!

[VegasNath]

=sumif((Right(A2:A298,3),"F E",D2:D298))

Any idea what is wrong with this? Can 'Right' be used with Sum If ??

[HansV]

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)

[VegasNath]

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)

[HansV]

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)