2

I need to compare an individuals age and salary against a table to pick an insurance premium. My age ranges are along the top, and salary ranges are along the side. Is there a way that a column/row can represent a range? (i.e. column B is 1-5, C is 6-10 etc.)

My idea is to use the INDEX and MATCH functions, but would rather not make a column for every age and a row for every salary.

Ram
  • 3,092
  • 10
  • 40
  • 56
user2320271
  • 97
  • 2
  • 2
  • 7

1 Answers1

3

You only have to show the "lower bound" for each range and then use MATCH with 1 as 3rd argument, e.g. with age ranges in B1:E1 as 1,6,11,16 then salary in A2:A5 as 0, 10000, 20000, 30000 then use this formula

=INDEX(B$2:E$5,MATCH(G2,A$2:A$5,1),MATCH(H2,B$1:E$1,1))

where G2 and H2 contain the specific salary and age to lookup respectively. e.g. looking up 13 and 23000 will match 13 with 11 in D1 and 23000 with 20000 in A4 and you get the value from D4

enter image description here

....yes, I know a 13 year old is unlikely to have a salary of 23000......!

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • This is a link to a Google Spreadsheet - When I try the formula, it returns the value in the cell directly above the cell that I want. https://docs.google.com/spreadsheet/ccc?key=0AlRB7kLDi__TdFJoZXU0QWRFZ3lzdlRmSU5fT0I3VFE&usp=sharing – user2320271 Jun 21 '13 at 21:12
  • The range of premiums should start at B3 not B2 so try making it `=INDEX(B$3:G$49, MATCH(J3,A$3:A$49,0), MATCH(I3,B$2:G$2,1))` – barry houdini Jun 21 '13 at 21:35