VBA to lookup Position for region by approval amount

I require a bit of vba to do a match or lookup of 2 values to a matrix of delegated authority by region and return the Position ID.

See sample data file attached.

Lookup value 1 is the Region
Lookup value 2 is the Amount

The lookup Amount must not exceed the matching matrix amount, but must be the lowest matching Position ID.

Assume that the matrix is sorted by Region, by Amount by Position ID.

Eg.
Region = A, Amount = 85,000 will return Position ID = Supervisor 1
Region = C, Amount = 1,185,000 will return Position ID = General Manager 3
Region = B, Amount = 500 will return Position ID = Admin 2

Any help would be much appreciated. Thanks

Hi there,

Try this:

Please Login or Register  to view this content.

HTH

Robert

Hello wotadude,

This doesn't require a VBA solution. The attach workbook has the following formula added to cell H2 (Result). The input cells are F2 and G2 (region and amount respectively).

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Both solutions proposed have pros and cons of what i wish to achieve.
Thanks for your responses to date.

The formula solution requires on going management of additional or named ranges.

The VBA solution requires constant change to accomodate the change in the Matrix.

I want the matrix to be the dynamic part (allowing for adding, changing or removing records as required) and the VBA to be the static part to return the result to a variable for futher use.

If the VBA solution could match off the Region and returning the Position ID for the least Amount that the lookup amount it is less than or equal to. With the result passed to a variable.

Hello wotadude,

The workbook nows has a dynamic VBA macro. The only restrictions are that your table has no empty rows in it and that each region must have the same amounts. Here is the code that was added.

Macro Code

Please Login or Register  to view this content.

Worksheeet Event Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross