Help writing a function to use a data table based on value

I need to write a function based on value entered in another cell, and to use the table a made on the sheet to determine what value to use in that cell.

example: if e46 is 55% then e48 equals 100%, or if g46 equals 45% then g48 equals 85%. how do I write that? The table is in cells S46 through T53. see copy and paste of table below. I tried to attach a pic of the table but it wont upload.


G.P. Comm. Rate
>20% 0%
20-29% 55%
30-39% 70%
40-49% 85%
50-59% 100%
60-69% 115%
70-79% 130%
79%< 150%

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Please read the yellow banner at the top of this page on how to attach a file.

It looks like a basic lookup function/lookup table using the approximate match option in one of Excel's lookup functions. Usual starting place for lookup functions is the VLOOKUP() function. Here's one of the few tutorial pages that has an example using the approximate match option: https://www.ablebits.com/office-addi...ximate-vlookup

May be:
In E48:

Please Login or Register  to view this content.

COpy to G48

With Bebo's suggestion applied to E48 and drag copied across:

Excel 2016 (Windows) 32 bit

	E	F	G	H	I	J
46	55%	52%	45%	50%	51%	40%
47
48	100%	100%	85%	100%	100%	85%

Sheet: Sheet1

I do not see a problem.

I'm not sure I understand what you are having trouble with. Here's what I did:

1) Perhaps it is just me, but, as one who works almost exclusively with real numbers, I despise working with text strings. So, the first thing I did was to replace the text strings in S46:S53 with actual numbers. 0 in S46, 0.2 in S47, 0.3 in S48, ... 0.7 in S52, 0.79 in S53.
2) I do not understand how you are calculating gross profit in row 46, so I have no comment there. In E48, I enter =VLOOKUP(E46,$S$46:$T$53,2,TRUE) and copy across.

If I have understood what you are trying to do, that should work for obtaining the commission rate.

Attach a workbook showing the error so we can work out what you've done.

Just attach the workbook with the problem - it'll be quicker!

Both LOOKUP() and VLOOKUP() [approximate match option] return the N/A error when lookup value is smaller than the first value in the lookup column. In both mine and Bebo's implementations, that would mean lookup value (the value in row 46) is less than 0. None of your example values for row 46 are less than 0, so neither lookup function should have returned N/A for the example values. Are your lookup values less than 0?

The lookup table is still text. Change it to this:

Excel 2016 (Windows) 32 bit

	S	T
45	G.P.	Comm. Rate
46	0.00	0.00
47	0.20	0.55
48	0.30	0.70
49	0.40	0.85
50	0.50	1.00
51	0.60	1.15
52	0.70	1.30
53	0.80	1.50

Sheet: Sheet1

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.