Help with looking up percentages based on criteria in a table

**gandyling** · 02-12-2016, 12:54 PM

Hello

Im have some trouble in getting something to work. Ive attached an example spreadsheet. On the table tab is different disc codes accross row 8 and then below that is spending range follwed by a % which would the discount %.

What im trying to achieve is on the Discount threshold tab is if you put in the disc code in cell b5 it will then give you the new discount % (taken from the table tab) in column H. This is based on the net inv sales (column d) along with the disc code.

Any help as always would be greatly appreciated.

Thanks

**etaf** · 02-12-2016, 04:05 PM

add a new row for 0
and then you can use

=VLOOKUP(E12,Table!$C$4:$H$12,6,TRUE)

see attached

EDIT
I have used the wrong column for the lookup gross instead of Net
=VLOOKUP(D12,Table!$C$4:$H$12,6,TRUE)

**gandyling** · 02-15-2016, 06:50 AM

Hello

Thanks for your help but it doesnt seem to change when I put a different code into cell B5. On the table tab there are different discounts depending on the discount code (Row 1). How can I can this to look at each specific table depending on the code?

Thnaks

**JeteMc** · 02-17-2016, 03:29 AM

Here is a solution that uses a helper table of sorts (which can be hidden for aesthetic reasons if desired). The main formula that drives the solution is:

Please Login or Register  to view this content.

D28 is the cell that records the column number of the discount code that is entered in B5 using a match formula. Since the discount percentage is two columns offset from the sales amounts, E28 increments D28 by 2. The formula from D29 and down can then be copied across to E29 and dragged down to get the percentages. Data validation applied to B5 takes away the chance that the user would input an incorrect discount code. The list for the data validation is in B28:B45. Here is a copy of Etaf's file from post #2 with the solution applied:
Copy of newdiscexample_etaf.xlsx
Let me know if you have any questions.

Help with looking up percentages based on criteria in a table

LinkBack

Thread Tools

Rate This Thread

Display

Help with looking up percentages based on criteria in a table

Re: Help with looking up percentages based on criteria in a table

Re: Help with looking up percentages based on criteria in a table

Re: Help with looking up percentages based on criteria in a table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Ranking a column of percentages based on the cells that meet criteria (between 100 to 103)

Populating table with data from another table in the same workbook based on criteria (VBA)

Table that shows percentages for each person based on table

Create criteria-based summary table from table rows across sheets

[SOLVED] Directing to a table based on two criteria, then to a value based on a value - exel 2003

[SOLVED] Formula that averages percentages based on said criteria

Calculating percentages based on multiple criteria

Bookmarks

Bookmarks

Posting Permissions