Dynamic Table Array in Vlookup

**lozg43** · 11-25-2012, 10:49 AM

Hi,

I've become somewhat accustomed to believing there is almost no problem that can't be solved in an Excel formula (without the need for VBA) but I'm really struggling to find the solution to a sheet that I'm currently working on.

I have a list of tax rates per country and income level and I need to be able to lookup the appropriate tax rate for any given country within a salary bracket.

So far I have achieved this with a Vlookup but only using a fixed row range for a single country.

I could create a large number of named ranges and then run a lot of conditional IF formulas on a country lookup and the salary figure however with more than 100 countries in the tax database this would be incredibly time consuming and quite inefficient.

So what I'm looking to achieve is a lookup on the salary figure that then references the lookup Table Array based on the country name.

As it stands I have a front sheet (with the salary figure and country) that references a "Tax Lookup" sheet which is laid out:

Column A - Country Name
Column C - Salary Band Low
Column D - Salary Band High
Column K - Tax Rate (the value to lookup)

As an example of what I'm working on at the moment - UK tax rates start on row 3515 and end on 3614 so have applied:

=VLOOKUP(Q1,'Tax Lookup'!C3515:L3614,9,TRUE) where Q1 is salary figure and the look up is manually specified to only look in the UK range of salaries.

However, what I really need to do is somehow incorporate a lookup that sets the Table Arrange range of rows based on the country (Cell P1 of the front sheet).

I did try to see if it was possible to set the table array through a further formula but it doesn't seem to work.

I get the feeling that an appropriate combination of Index and Match might be able to solve this but after spending a good couple of hours at work trying to find a solution I think it's time to open this up to people far more clever than myself.

Can someone please help me?

Thanks!

P.S. I need to do this without using VBA as if all possible.