I am using this macro but I have two problems when it runs
1 - If the text being searched for isnt there the it still returns a value
which is wrong
2- If the text being searched for is there but the result fiel is empty it
returns a zero i need it to leave the cell empty

Thank you

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub

Mire,

It's not working because the first lookup ("WBM"=True) is a kleft lookup and
you can't do that you need index match.

Heres a corrected formula non R1C1 which you should be able to convert

=IF(C15="","",IF(C2="WBM",INDEX(A:A,MATCH(C26,IV:I V,FALSE),1),LOOKUP(C15,Densities!D:D,Densities!E:E )))

Mike

"Miree" wrote:

I am using this macro but I have two problems when it runs
1 - If the text being searched for isnt there the it still returns a value
which is wrong
2- If the text being searched for is there but the result fiel is empty it
returns a zero i need it to leave the cell empty

Thank you

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM""," & vbLf & _
"IF(ISERROR(LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4])),"""",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4])),"
& vbLf & _
"IF(ISERROR(LOOKUP(RC[-2],Densities!C[-1],Densities!C)),"""",LOOKUP(RC[-2],Densities!C[-1],Densities!C))))"


--
__________________________________
HTH

Bob

"Miree" wrote in message
...
I am using this macro but I have two problems when it runs
1 - If the text being searched for isnt there the it still returns a value
which is wrong
2- If the text being searched for is there but the result fiel is empty it
returns a zero i need it to leave the cell empty

Thank you

"=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))"
Range("H21").Select
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
End Sub