Ultimate lookup formula

[Clint]

Hi , my name is Clint

I am new in the world of vba and rather enjoy the possibilities that you can use it for. I am trying to use the ultimate lookup but I am experiencing problems. it keeps giving me an #N/A. Can somebody please assist as I am not sure what I am doing wrong??
Please see Sheet1

Regards
Clint

 

[bosco_yip]

The "Surname" Column A data contain a lot of space

After cleared the space, the formula will work fine.

p.s. the formula is an array formula, confirm entered with CTRL+SHIFT+ENTER

Regards

 

[Clint]

Hi Bosco_yip

Thank you very much it works!!! Just another Q, would I be able to apply this formula into a vba code. I have sample code in the attached wb which works but as you can see I would like to do a lookup using more criteria.

Regards
Clint