Formula with to select a percentage from a table

[MigueX]

Hello Guys,

I need to select a percentage from a table / Values based on a cell.
1st i need to get the Region % and then the Manager % so i can get the value to apply.

help.JPG

Edit Can seem to attach the file
Edit2: Attached the file!
Edit 3: solved

Thanks in advance.

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[MrShorty]

It looks like it should be a relatively simple 2D lookup formula. Are you familiar with the INDEX() [https://support.office.com/en-us/art...2-b56b061328bd ] and MATCH() [https://support.microsoft.com/en-us/...rs=en-us&ad=us ] functions?

1) Are you required to use those text strings in A4:A7 and B3:E3? Could you replace those with numbers? I would start by replacing those text strings with suitable numbers. A4 would be -1, A5 would be -0.05, A6 would be 0, A7 would be 0.05. Same values in B3:E3.
2) To find the row # based on a %region value, use a MATCH() function. MATCH(B11,$A$4:$A$7,1). Note the mix of relative and absolute references and also note the use of 1 in the 3rd argument telling Excel to use an approximate match lookup algorithm (see help file).
3) Use a similar MATCH() function for the column #. MATCH(C11,$B$3:$E$3,1)
4) Use an INDEX() function to return the corresponding value from the main body of the lookup table. =INDEX($B$4:$E$7,MATCH(B11,...),MATCH(C11,...))

Would something like that work for you?

[MrShorty]

Testing in your sample file, and I observe that you have preformatted all of your cells as text, which means you cannot enter formulas and any number entries convert to text. You will need to change this and reenter numbers so that you can enter numbers and formula into cells. If there is some outside reason that you are required to work with text rather than numbers, please explain so that solutions can take that requirement into account.

[MigueX]

Hello MrShorty,

I uploaded the file with the values in %.
i dont need them to be in text, only need to get the values

Tx!

[MrShorty]

I won't have access to Excel until tomorrow. Have you tried my suggestion above?

[MigueX]

Hello,

Tried your suggestion but cant seem to make it work

i attached the file on this reply.

[AliGW]

You failed to follow Mr Shorty's very clear instructions and only created one MATCH function - you need two, as this is a 2D lookup.

Try this:

=INDEX($B$4:$E$7,MATCH(B11,$A$4:$A$7,1),MATCH(C11,$B$3:$E$3,1))

You may need semi-colons in place of the commas for your Portuguese locale.

[MigueX]

Cool,

That worked!

Gonna test it more then going to give feedback!!

Thanks alot

Edit:

[AliGW]

No, the formula I gave you finds a cross reference point: the first MATCH looks down the region axis, and the second MATCH looks along the managers axis. The INDEX section then finds cell D6 at the intersection and returns 0.075 (8%).

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1
2	Region %	Managers %
3		-1%	-0.05%	0%	6%
4	-1%	0%	-10%	0%	0%
5	-0.05%	-15%	-5%	5%	3%
6	0%	-10%	0%	8%	10%
7	6%	-5%	0%	10%	20%
8
9
10	Name	% Region	% Managers	Aplyed %
11	Name 1	5%	1%	8%
12	Name 2	10%	5%	10%
13	Name 3	-10%	11%	#N/A

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	D
11	=INDEX($B$4:$E$7,MATCH(B11,$A$4:$A$7,1),MATCH(C11,$B$3:$E$3,1))

Sheet: Sheet1

[AliGW]

Cool,

That worked!

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

Also, 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.

[MigueX]

Hey,

Why the negative numbers like -10% are getting a error?
All the -1 number have to be treated the same way.
Should i add more number in the table?

[AliGW]

Change the boundaries, e.g.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1
2	Region %	Managers %
3		-100%	-0.05%	0%	6%
4	-100%	0%	-10%	0%	0%
5	-0.05%	-15%	-5%	5%	3%
6	0%	-10%	0%	8%	10%
7	6%	-5%	0%	10%	20%

Sheet: Sheet1

[JohnTopley]

I think your 1% should 10% and 0.05% should be 5%

[AliGW]

Not sure I agree, John - they are negatives for sure (look at the results table - final row criteria).

[JohnTopley]

Look at original post. 0.05 (in the table) is the numeric equivalent of 5% so i think Numbers vs % are mixed.

[AliGW]

Sorry - where do you see 0.05 in the original post? I see less than minus 5%.

[JohnTopley]

I don't see 0.05 - it is 5% but row 5 in your (OP's) table has -0.05% not 5%.

[AliGW]

Ah, yes! Should be -5% (or even -6%).

[MigueX]

Thanks for the reply's!

1st image is the original table
Even if i extend the table to -8% all values bellow -8 return error.
Positive number all work good pass the >5%

[AliGW]

Even if i extend the table to -8% all values bellow -8 return error.

Your first entry needs to be LOWER than the lowest possible value you'd want to look up, so if your lowest value to look up is -90, you need to start with -90. Anything lower will return an error.

[MigueX]

It worked like a charm!

thanks alot for all your help

[AliGW]

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

Also, 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.