Any Excel exprts out there?

[Andy™]

i havea formula of

=LOOKUP(D2,$A1:$A21, $B1:$B21)

if there is nothing in 'd2', it shows #N/A. is there any way to make it show a blank box, or '0' instead?

 

[NozSpark]

Can you not put it in a "if" formula..

if(d2="";"";lookup(d2,$a1:$a21,$b1:$b21))

don't know if it'll work though?

 

[Andy™]

Can you not put it in a "if" formula..if(d2="";"";lookup(d2,$a1:$a21,$b1:$b21))

don't know if it'll work though?

that doesnt work. get an nessage saying formula contains an error

anyone else got any ideas?

 

[NozSpark]

try this

=IF(d2="";0;LOOKUP(d2;a1:a21;b1:b21))

there is a subtle difference

 

[Andy™]

try this=IF(d2="";0;LOOKUP(d2;a1:a21;b1:b21))

there is a subtle difference

still get an error?

=IF(d2="";0;LOOKUP(d2;a1:a21;b1:b21))

red bit is highlighted as error

 

[NozSpark]

sorry, don't have excel.... use open office

and the formula works with open office.

I think you will have to work out a way to get "" as a numerical value... maybe <1

 

[Andy™]

sorry, don't have excel.... use open officeand the formula works with open office.

I think you will have to work out a way to get "" as a numerical value... maybe <1

still cant get it to work.

also discovered that my pda which i plan to put it on wont take 'lookup', but will take 'vlookup', so in E2, i now have

=VLOOKUP(D2,A1:B21,2,TRUE)

 

[Andy™]

got it working.... i have made a value of '0' in a1 & b1, it now displays '0' if D2 is blank

 

[Andy™]

Admin - how do i do this 'solved' thingy?

 

[NozSpark]

edit your first post

 

[Watchdog]

[quote name='Andy

 

[JUD]

Try this

=IF(CELL("type",D2)="b",0,LOOKUP(D2,$A1:$A21,$B1:$B21))

If Cell D2 is blank ("b") enter 0 else perform LOOKUP.

If you want to leave it blank instead of having the 0 replace the 0 with ""

 

[Andy™]

thanks, that seems to be working