search formula

[tweety127]

hi,

what seems to be wrong in this formula or is this formula valid?

=IF(VLOOKUP(RIGHT($C3,LEN($C3)-FIND("^^",SUBSTITUTE($C3,"/","^^",LEN($C3)-LEN(SUBSTITUTE($C3,"/",""))))),'DATA SOURCE'!A$1:C$84,2,FALSE),IF(VLOOKUP(LEFT($C3,LEN($C3)-FIND("^^",SUBSTITUTE($C3,"/","^^",LEN($C3)-LEN(SUBSTITUTE($C3,"/",""))))),'DATA SOURCE'!A$1:C$84,2,FALSE)))

thanks

[rylo]

Hi

Unless there is something missing when it was posted, the formula is incomplete. You have nested if statements but you don't do anything with them. The following completes the formula, but is essentially meaningless.


=IF(VLOOKUP(RIGHT($C3,LEN($C3)-FIND("^^",SUBSTITUTE($C3,"/","^^",LEN($C3)-LEN(SUBSTITUTE($C3,"/",""))))),'DATA SOURCE'!A$1:C$84,2,FALSE),IF(VLOOKUP(LEFT($C3,LEN( $C3)-FIND("^^",SUBSTITUTE($C3,"/","^^",LEN($C3)-LEN(SUBSTITUTE($C3,"/",""))))),'DATA SOURCE'!A$1:C$84,2,FALSE),"xx","yy"))


hth

rylo

[tweety127]

Hi,

thanks for the response...

what i am trying to do with that formula is. if the first condition is true, it will return the value found in the vlookup hence, if not true it will do the second lookup....

[rylo]

Hi

I think you are going to run into trouble with the length of the formula required.

Is it possible to:
(a) get the left / right components into separate cells
(b) do the vlookups on each component
(c) do a test on the results and evaluate?

If you really need to do it in one formula, then I think you will have to resort to building your own custom function.


HTH

rylo