Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ivygirl
 
Posts: n/a
Default Please Help! vlookup & match with multiple variables


Hi all,

I'm trying to develop a rating worksheet for an insurance agency. The
rating tables are broken down first by territory (group of counties)
and then the premium is shown depending upon the class (group of
specialties) and limits desired. So, for example, for Territory 1, I
have a rating table with classes 1 through 8 and different limit
selections across the top, complete with corresponding premiums, like
so:

A B C D
Class 200/600 500/1MM 1MM/3MM
1 2000 4000 6000
2 2500 4500 6500

I've constructed a user input sheet that allows them to select the
appropriate variables (county, limits, etc) from drop down boxes and
then I have a hidden worksheet converting these selections to usable
data from additional tables in the background, i.e.,

A B
Archer 1
Radiology 2
200/600 1

I can construct a vlookup/match formula to tell excel to match the
class shown in B3 with the limits shown in B4 but I can't figure out
how to make it first select the appropriate table depending upon the
county selected in B2. In other words, I need excel to say "this is
Archer county (B2) so go look at Range X rate table and then
vlookup/match according to class(b3) and limits(B4) to return the right
premium".

Any ideas?

Thanks so much :)

Kate


--
ivygirl
------------------------------------------------------------------------
ivygirl's Profile: http://www.excelforum.com/member.php...o&userid=30204
View this thread: http://www.excelforum.com/showthread...hreadid=498863

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
paul
 
Posts: n/a
Default Please Help! vlookup & match with multiple variables

you need to give each county table a name(ie create a named range) and then
use the indirect function to call that name in your index/matcn/lookup
formulas,so your lookup formula will look sometning like this
index(INDIRECT(R$237),MATCH($C8,INDIRECT(R$238),-1),MATCH($E8,INDIRECT(R$239),-1)).
the names are created from your dropdown boxes and the named ranges match.So
instead of a fixed table the table will change evry time you change the
county.I copied my formula from a longer one so hopefully i got all the
corect brackets.Any queries post back


--
paul
remove nospam for email addy!



"ivygirl" wrote:


Hi all,

I'm trying to develop a rating worksheet for an insurance agency. The
rating tables are broken down first by territory (group of counties)
and then the premium is shown depending upon the class (group of
specialties) and limits desired. So, for example, for Territory 1, I
have a rating table with classes 1 through 8 and different limit
selections across the top, complete with corresponding premiums, like
so:

A B C D
Class 200/600 500/1MM 1MM/3MM
1 2000 4000 6000
2 2500 4500 6500

I've constructed a user input sheet that allows them to select the
appropriate variables (county, limits, etc) from drop down boxes and
then I have a hidden worksheet converting these selections to usable
data from additional tables in the background, i.e.,

A B
Archer 1
Radiology 2
200/600 1

I can construct a vlookup/match formula to tell excel to match the
class shown in B3 with the limits shown in B4 but I can't figure out
how to make it first select the appropriate table depending upon the
county selected in B2. In other words, I need excel to say "this is
Archer county (B2) so go look at Range X rate table and then
vlookup/match according to class(b3) and limits(B4) to return the right
premium".

Any ideas?

Thanks so much :)

Kate


--
ivygirl
------------------------------------------------------------------------
ivygirl's Profile: http://www.excelforum.com/member.php...o&userid=30204
View this thread: http://www.excelforum.com/showthread...hreadid=498863


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Please Help! vlookup & match with multiple variables

"ivygirl" wrote...
....
I'm trying to develop a rating worksheet for an insurance agency. The
rating tables are broken down first by territory (group of counties)
and then the premium is shown depending upon the class (group of
specialties) and limits desired. So, for example, for Territory 1, I
have a rating table with classes 1 through 8 and different limit
selections across the top, complete with corresponding premiums, like
so:

....

If you arrange the separate tables for each territory in a regular manner,
you could eliminate the need to use volatile functions like INDIRECT. For
example, with only 8 classes in each table, you could have column headers in
rows 1, 11, 21, etc., with the left column/top row cell in each table
holding the territory name (if they're hidden, you don't need a 'Class'
header in those cells). Add a 2-column table of territories in the same
worksheet with territory names in the left column and formulas giving the
row index in which the territory is found, e.g., with the table in columns X
and Y beginning in row 1,

X1:
SomeTerritoryName

Y1:
=MATCH(X1,$A:$A,0)

Fill Y1 down to match the entries in column X. If this worksheet were named
TBLS, use formulas in other worksheets like

=VLOOKUP(ClassEntry,
INDEX(TBLS!$A:$A,VLOOKUP(TerritoryEntry,TBLS!$X:$Y ,2,0))
:INDEX(TBLS!$D:$D,VLOOKUP(TerritoryEntry,TBLS!$X:$ Y,2,0)+8),
MATCH(LimitEntry,TBLS!$B$1:$D$1,0),0)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Partial String Match Using VLOOKUP cdhmotes Excel Worksheet Functions 4 December 26th 05 11:26 PM
vlookup, multiple values, sum values into one cell?? Phillips L Excel Worksheet Functions 4 November 9th 05 01:31 AM
can vlookup be forced to make a case sensitive match? alan Excel Discussion (Misc queries) 1 September 22nd 05 12:59 PM
how do I use vlookup for multiple occurrences of the same value Edith F Excel Worksheet Functions 15 April 29th 05 06:12 PM
Index Match With 3 Variables Scooterdog Excel Worksheet Functions 5 January 2nd 05 08:05 PM


All times are GMT +1. The time now is 11:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"