#Value error

[alirulez]

Hi guys

Id be grateful if you can help. Im getting a value error on this formula and I cant work out why this is the case

The Vlookup returns the value 421 - So Id like the formula to resolve to =ROUND('BlueCross Rates'!U421,3)

=ROUND("'BlueCross Rates'!U" & VLOOKUP(J3,Workings!J14:K37,2,FALSE),3)

The Vlookup returns the value 421 - So Id like the formula to resolve to

=ROUND('BlueCross Rates'!U421,3)

When I click evaluate, the last formula syntax before it fails to #Value is

ROUND("'BlueCross Rates'!U421,3)

Note the double quotation marks after the first bracket.

Any help would be much appreciated
Best
A

 

[Joe4]

If you are building the range reference by formula, then you need to use the INDIRECT function, i.e.

=ROUND(INDIRECT("'BlueCross Rates'!U" & VLOOKUP(J3,Workings!J14:K37,2,FALSE)),3)

See: http://www.contextures.com/xlFunctions05.html

 

[alirulez]

Fantastic. That works - I'd have never been able to solve that on my own. Cheers

 

[Marcelo Branco]

Or avoiding the volatile INDIRECT function

=ROUND(INDEX('BlueCross Rates'!U:U,VLOOKUP(J3,Workings!J14:K37,2,FALSE)),3)

M.

 

[Joe4]

You are welcome.