Vlookup Multiple Criteria with VBA UDF

Associated Files Download Links


Vlookup Multiple Conditions Using VBA

Consider the following data table:

128 multiple vlookup

The standard Vlookup function within Excel has the following format:

VLOOKUP(“”Mark”, B6:G12”,2,FALSE)

Which will return “Brown”.

However, what about if we wanted to look up on 2 or more conditions e.g the first name, last name and the age in the above table ? The following UDF allows us to do this:

It has the following syntax:

ThreeParameterVlookup(Data_Range, Col , Parameter1, Parameter2 , Parameter3 )

Where:
• Data_Range is the range of the data
• Col is an integer for the required column
• Parameter1, Parameter2 and Parameter3 are the values from the first three columns respectively

So that:

=ThreeParameterVlookup(B6:G12,6,”Mark”,”Brown”,7) will return ”Tolworth” as this is a match on “Mark”, “Brown”, and 7 and a reference to the 6th column

Note that this function will also worked with (dynamic) named ranges as well:

=ThreeParameterVlookup(named_range,6,”Adrian”,”White”,7) will return “Chessington” where we have set up the named range “Named_Range”.

If Excel can’t locate a match then “N/A” is returned by default. In fact, the function assumes a value of N/A at the outset and then only changes when it finds an exact match.

Also if the value of Col exceeds the number of columns then a Reference error occurs.

To download the .XLSM file for this tutorial, click here

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]alt text

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]