Excel vlookup custom function with multiple values

[dorabajji]

Hi,
I have attached the file revised..
In this AE sheet, sheet3, sheet 2

In sheet 3 i use the UDF-custom function to get match values separated by comma.. What is want is when there is more than 1 value, i need it as a dropdown option..
When the answer is single value, just the value..

and when the value is not matching ie. which everrow 0 is occuring or NA error..
Then should be looked for the substring checking in sheet 2 -col C and matching values should give col D same row value....
So sheet 3 should first look in AE, if not available there it should look in sheet 2..
And for AE the comparison is Sheet 3 col a with ae sheet col a and b..
in sheet 2 comparison is sheet 3 col b with sheet 2 col c and return value should be from d//


And in the same file, i have another question.. I want to create additional sheet dynamically..
In that case, i need a done button (command) also for the user to press it ..
is it possible to add the button also dynamically when the new page is created?

[CK76]

Change your UDF to something like below, as With Application... End With part is unnecessary and just slows down code.

Please Login or Register  to view this content.

Then call the function in another sub. Since UDF can't be used directly in Data Validation formula.

Please Login or Register  to view this content.

[dorabajji]

Hi Sir,
I want also an additional check in this , as mentioned above.. when the answer is o or NA error, it should check sheet 2 for col C and return Col D value..
But in sheet 3 the criteria shifts from A to B.. when it checks sheet 2..
after the user gives the option and also the value updation, this is copied to a new sheet dynamically and a done button is created there with macro for exporting included..
I want to know how to add dynamically done button in the new dynamic sheet and also this additional check, how can it be incorporated..
What i did is i used instr and checked the other sheet for each word substring and used if not equals 0 and na.. then return value..
is there any simple one for this.. to check and for done, i kept in first sheet as i dont know how to add it to dynamic page.. can you help me with that..

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No further help to be offered until this moderation request has been fulfilled.)

[dorabajji]

Hi CK76,
I have one doubt in the above suggestion, you had made..
you have assigned x variable and added validation?
I have given additional check that if there is a comma, no validation. But how do i still have the custom function calculated for the particular cell in the above code.

[dorabajji]

Please Login or Register  to view this content.

Hi,
In the above UDF, can the values derived, be when there is no more than 1 word. it shows many commas in the cell..
I want the cell to be blank, if there is no string or character other than , exist in the function..

can this udf be modified?