Formulas within formulas

[jason-t]

Hi all i need some little help what i am trying to do is in one cell get the answer for this. I have three cells b2,c2,d2 only one has value and depending on what one it is it is true for that cell now when its true i want it to look up on sheet2 two columns now if b2 is true then look up sheet2 column a,b and if c2 is true then look up sheet 2 column a,c and if d2 is true then look up sheet 2 a,d now the formula i am trying to us looks like this

=IF(B2,(HLOOKUP(A1,SHEET2!A,B)))+IF(C2,(HLOOKUP(A1,SHEET2!A,C)))+IF(D2,(HLOOKUP(A1,SHEET2!A,D)))

But it does not want to work can any one help???/

 

[Gerald Higgins]

Hi, welcome to the board.

First, do B2/C2/D2 actually contain the logical TRUE values ?
It looks, from your formula, as if they do, so I'll assume they do.

Maybe you need something like this.

=if(b2,vlookup(a1,sheet2!A:B,2,false),if(c2,vlookup(a1,sheet2!A:C,3,false),
if(d2,vlookup(a1,sheet2!A:D,4,false),XXX)))

Replace XXX with whatever you want to do if b2, c2 and d2 are all false.

Make sure you understand what the "false" part does.