Customa function for nested if and vlookup

QuestionEdit

Once again look forward for help. I finally decided to learn vba macro and as expected started to face problem initially. Ok, Lets get it simple. i know how to use if condition & vlookup function in vba alone. but am having difficulties in converting my following formula as a custom function combining them together. Here is the folrmula: =IFERROR(IF(B2="Boy",VLOOKUP(A2,$H$2:$H$29,1,FALSE),VLOOKUP(A2,$J$2:$J$10,1,FALSE)),"Name Not Present in Either List") Please see the attachment for better understanding. Anyways here the problem. I have a list of Boys and girls in column A and specified their gender in column B. There is two table in column H and J holding the names of boys and girls respectively. now i am using a condition that if cell b2 is boy then the name in cell a2 should be looked in boys table else it should look at the girls table & finally if no name is found then to give a comment saying "Name not Present"

So could you please help me in writing this macro. Thank you very much once again.

Sami

AnswerEdit

Sami,

Usage in A2 would be:

=checknames(B2,A2,$H$2:$H$29,$J$2:$J$10)

and the formula can be drag filled down the column.

Public Function CheckNames(r1 As Range, r2 As Range, r3 As Range, r4 As Range) Dim s As Variant s = Application.WorksheetFunction.IfError( _

 IIf(r1 = "boy", _
 Application.VLookup(r2, r3, 1, False), _
 Application.VLookup(r2, r4, 1, False)), _
 "Name Not Present in Either List")

CheckNames = s End Function

Note that IFError only works in Excel 2007 Also, you can't use vlookup with the worksheet qualifier if you want to use IFError. When you qualify vlookup with worksheetfunction, it raises a real trappable error. You can use it that way, but the code would have to be written differently.

It seemed like you wanted to match the version you posted as closely as possible - so I went with the above. It can be programmed much differently and the IFERROR is not required in that case. (In VBA, there is no particular advantage to getting everything in one line like there is in a formula.).