Looking up multiple variables in a table and giving the results

**mintribe** · 02-27-2012, 08:20 AM

I am trying to put together a discount calculator. There are 3 variables: Pricing Type, Partner Type and Discount level. Each one of these has several options

Pricing Type: Base, Incumbent, DR sub 1,000, DR over 1,000
Partner Type: R, S, G, P
Discount Level:
1-24%
25%-34%
35%-44%
45%-64%
65%-84%

What I would like to do is have drop down boxes for each of the above (I can do that bit in attached spread sheet) and then from that calculate the correct discount base on the three variables. e.g. Base, R, 1-24% would then give me the correct discount from a table.

I have attached a spread sheet - disc calc.xlsx - with the relevant information would be grateful if some one could show me how to do it?

Many Thanks

**Richard Buttrey** · 02-27-2012, 08:29 AM

Hi,

See the attached,

Regards

**Andrew-R** · 02-27-2012, 08:32 AM

If you change your drop-down list values so that the exactly match the headers in row 1 of your table then you can use this formula:

=INDEX(B3:Q8,MATCH(C15,A3:A8,0),MATCH(C13,INDIRECT(ADDRESS(2,MATCH(C11,1:1,0)) & ":" & ADDRESS(2,MATCH(C11,1:1,0)+3)),0))

**mintribe** · 02-27-2012, 08:37 AM

thank you - I suppose it is easy when you know how

Apreciate your help

**Richard Buttrey** · 02-27-2012, 08:39 AM

Indeed, it always is!

Applies throughout life.....

Looking up multiple variables in a table and giving the results

LinkBack

Thread Tools

Rate This Thread

Display

Looking up multiple variables in a table and giving the results

Re: Looking up multiple variables in a table and giving the results

Re: Looking up multiple variables in a table and giving the results

Re: Looking up multiple variables in a table and giving the results

Re: Looking up multiple variables in a table and giving the results

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions