Multiple VLOOKUP Tables

  • Thread starter Thread starter ANDYGM
  • Start date Start date
A

ANDYGM

I am using Excel 97 and I have simplified the description of the proble
for clarity.

I am writing a spreadsheet to calculate insurance premiums for Houses
Offices and Shops. Within each category there are different covers
e.g. All Risks; All risks ex Subsidence, etc.

I have one cell where the user picks the type of building (e.g
Offices) and another where they pick the cover (e.g. All Risks).

In the next cell, the sum insured is entered and in the cell next t
that I am using the VLOOKUP function to provide the rate to b
applied.

Now, in order to do this I need to have three different table arrays
HOUSES, OFFICES and SHOPS. Each has the covers in the left hand colum
and in the right hand column are the rates to be applied.

My problem is that to select the correct table, I need to select th
Lookup_value (which is in cell C4 - e.g. All Risks) and the Table_arra
(which is in cell B4 - e.g. Offices). However, although a Lookup_valu
of C4 returns the correct answer, a Table_array value of B4 return
"#VALUE!". I have tried several variations of B4, e.g. T(B4), bu
nothing works.

Can you offer any suggestions?

Thanks.

And
 
There are free download files to illustrate this in many places. Two that
come readily to mind are the "Named Range" file at www.myweb.cableone.net
and an excellent description/discussion of the problem and (I believe) a
file download at www.contextures.com.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
ANDYGM said:
I am using Excel 97 and I have simplified the description of the problem
for clarity.

I am writing a spreadsheet to calculate insurance premiums for Houses,
Offices and Shops. Within each category there are different covers,
e.g. All Risks; All risks ex Subsidence, etc.

I have one cell where the user picks the type of building (e.g.
Offices) and another where they pick the cover (e.g. All Risks).

In the next cell, the sum insured is entered and in the cell next to
that I am using the VLOOKUP function to provide the rate to be
applied.

Now, in order to do this I need to have three different table arrays:
HOUSES, OFFICES and SHOPS. Each has the covers in the left hand column
and in the right hand column are the rates to be applied.

My problem is that to select the correct table, I need to select the
Lookup_value (which is in cell C4 - e.g. All Risks) and the Table_array
(which is in cell B4 - e.g. Offices). However, although a Lookup_value
of C4 returns the correct answer, a Table_array value of B4 returns
"#VALUE!". I have tried several variations of B4, e.g. T(B4), but
nothing works.

Can you offer any suggestions?

Thanks.

Andy


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Try using INDIRECT() for the table-array part

Put in say, D2 : =VLOOKUP(C2,INDIRECT(B2),2,FALSE)

where:

in C2 = Cover (e.g.: "All Risks", "All Risks ex Sub", etc)
in B2 = Category (e.g.: "Houses", "Offices", "Shops")

and you have 3 named ranges: Houses, Offices and Shops

Note: The 3 named ranges are used as the table arrays in
the vlookup. As described in your post, each named range
has 2 cols: the covers in the left hand column
and the rates to be applied in the right hand column.

D2 will return the rate for the Cover type in C2
and the Category type in B2

Format D2 to suit (e.g.: %)

Copy D2 down col D

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
----------------------------------------------------------------------------
ANDYGM said:
I am using Excel 97 and I have simplified the description of the problem
for clarity.

I am writing a spreadsheet to calculate insurance premiums for Houses,
Offices and Shops. Within each category there are different covers,
e.g. All Risks; All risks ex Subsidence, etc.

I have one cell where the user picks the type of building (e.g.
Offices) and another where they pick the cover (e.g. All Risks).

In the next cell, the sum insured is entered and in the cell next to
that I am using the VLOOKUP function to provide the rate to be
applied.

Now, in order to do this I need to have three different table arrays:
HOUSES, OFFICES and SHOPS. Each has the covers in the left hand column
and in the right hand column are the rates to be applied.

My problem is that to select the correct table, I need to select the
Lookup_value (which is in cell C4 - e.g. All Risks) and the Table_array
(which is in cell B4 - e.g. Offices). However, although a Lookup_value
of C4 returns the correct answer, a Table_array value of B4 returns
"#VALUE!". I have tried several variations of B4, e.g. T(B4), but
nothing works.

Can you offer any suggestions?

Thanks.

Andy


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
You could make up a table which includes all the rates
from each table and have the building type joined to the
cover type in the first column. This could be linked to
your three separate, more readable, tables or you could
have it as the only place were your rates are stored. Your
formula in final cell will be some thing like

= VLOOKUP(BuildingType & CoverType & Amount, RateTable,2,TRUE)

Sort this table and it should work.

If you use a drop down box for the user to select the building type
and cover type you will eliminate the user entering "home" instead
of "house" etc.

Hope this helps.

Steve.

ANDYGM wrote
 
Thanks to all who helped, your suggestions to use the INDIRECT functio
works well.

Cheers!

And
 
Back
Top