PDA

View Full Version : VBA Nested Lookup as a UDF



msquared99
07-31-2012, 06:53 AM
I was wondering how to take the following formula: =_xlfn.IFERROR(LOOKUP(2,1/(($A$2:$A$7=$B11)*($C$2:$C$7<=D$10)),$B$2:$B$7),LOOKUP(2,1/($A$2:$A$7=$B11),$B$2:$B$7))

and make it a user defined function?

I would like the user to be able to select the crieteria.

BTW, I'm not familiar with building UDF in VBA (2007 Excel).

I've attached a sample.

Thanks!

Bob Phillips
07-31-2012, 08:47 AM
Reverse columns D:H in the second table and use

=INDEX($D$11:$H$16,MATCH($A2,$B$11:$B$16,0),MATCH($C2,$D$10:$H$10,-1))