Thread: vlookup and if = #Value!

I'm using multiple worksheets in one big spreadsheet to automatically work out job pricing. I need excel to look at the specified stock and decide which of two prices to use.

Stock options are in worksheet B, and column A has either a 'y' or an 'n' as a filter. The prices are in worksheet C, using columns A/B and E/F respectively (first column is a quantity for vlookup to find, second column is the price).

I need to get the formula to look at the stock, then if it's got a y, use the pricing in columns A/B. If it's got an 'n' I want it to use the pricing in E/F.

This is what I'm trying to get working:

=VLOOKUP($A$5,STOCK!$A$1:$B$26,(IF(STOCK!$A:$A,"y(VLOOKUP($B$4,COLOUR_A4!$A:$B,2,FALSE))","n(VLOOKUP ($B$4,COLOUR_A4!$E:$F,2,FALSE))")),FALSE)

But it's just returning a #VALUE! error, and I can't figure out why. Any help would be appreciated - I've been staring at this for ages.

Thanks for that - it's returning a #N/A instead of a #VALUE!, but that's progress - will take another look later today.