Return value and assign cell within UDF

Question

I always need to compare two worksheet base on a keys(more than one cells value) and then return more than one cells value from 2nd worksheet. To achieve it, I need to concaternate the cells into one cell in both file before using the multiple vlookup function to return values.

Then I try to build a UDF which allow to define the search keys(from 1st worksheet) and search from keys(from 2nd worksheet) and assign the value to different cells at the same time if record matched. Unfortunately, I found it violate the Excel UDF rule that you can't set value to a cells within a return UDF.

I don't know why Excel has such limitation. It knows it can be done within a sub procedure. But I really want to use a UDF so that I can return pass parameter to a function easily and return a indicator from the function at the same time.

Please if you have any good idea how to resolve it.

Answer 1

The reason why a UDF can only return values to the cells it occupies is because of the way Excel's recalculation logic works: returning a value to different cells would mess up the dependency trees and calculation chains.

You can return multiple values from a UDF by making the UDF a multi-cell array formula. But the cells have to be contiguous.
see http://fastexcel.wordpress.com/2011/06/20/writing-efiicient-vba-udfs-part5-udf-array-formulas-go-faster/