EXCEL Lookup a value, compares it against a range and returns a separate value from another cell. (File attached)

If I change the value in CELL "D4" (TABLE 1) the value in CELL "M6" (TABLE 2) should update based on a lookup to the scoring (Table 3) returning the relevant value between C11 to H11.                                    
E.g. If the value in D4 was changed to "4" the value in M6 would change to "2" (D2) based on the associated lookup.                                     
This formula will apply elsewhere to the line 13 and 14 ranges also so should lookup within the specific range listed.
Alistair_MairAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you provide a sample file? it looks confusing if table1, table2 and table3 all in the same worksheet.
0
Alistair_MairAuthor Commented:
Hi Ryan,

attached file, sorry i thought i had attached it previously. I have put table one and two in worksheet one and table three in worksheet two.
LookupBook2.xlsx
0
Alistair_MairAuthor Commented:
Additionally, the lookup values may be an integer or a fraction and the "score" value returned should be the closest relevant value against the lookup table,
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
E.g. If the value in D4 was changed to "4" the value in M6 would change to 2 (E3 worksheet 2) based on the assciated lookup .
for the value in D4, is this 4% or 4 ?

so is that you only need to lookup the values for:

Total Cost Reward  
Total Cost Risk

?
0
Alistair_MairAuthor Commented:
Would prefer the entry in D4 was 4% rather than just 4, but really only for display purposes.

Profit
Total Cost Reward
Total Cost Risk

thanks
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I guess we may need to change the layout of scoring table so that we can get the values more easily.

SnapShot.png
check the attached sample for illustration.
LookupBook2_b.xlsx
0
Alistair_MairAuthor Commented:
Hi Ryan,

thanks, things are looking up, but while I can change the input value field to include one decimal and it returns the relevant correct values for all entries over 1% (Including fractions) it doesn't for any value under 1% as the input value reverts to a whole decimal percentage i.e. ".9%" reverts to "90%".
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it should work well for percentage under 1% as well.

just format the cells to include 1 decimal points.

SnapShot.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alistair_MairAuthor Commented:
I tried that (Changed the Test Profit Input) to include one decimal point but when i input ".9" it reverts to 90%? Can you send me your revised spreadsheet and i can see if there is a something I'm missing. Thanks.
0
Rob HensonFinance AnalystCommented:
Unfortunately that is an issue with how excel interprets your entry.

For entries below 1% enter them with the % symbol, ie 0.9% rather than just 0.9 or enter as 0.009 and it will be recognised.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as requested, pls see attached.

when your cell already been formatted (Number Formatting) as percentage, you can directly enter the figure directly, and it should be auto-formatted for you the value in percentage.

Untitled.pngLookupBook2_c.xlsx
0
Rob HensonFinance AnalystCommented:
@Ryan - still gives 90% when entering 0.9, unfortunately it is standard Excel behaviour.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@Rob,

what i mentioned was this:

Formatting empty cells    If you apply the Percentage format to cells, and then type numbers into those cells, the behavior is different. Numbers equal to and larger than 1 are converted to percentages by default; and numbers smaller than 1 are multiplied by 100 to convert them to percentages. For example, typing 10 or 0.1 both result in 10.00%. (If you don't want to display the two zeros after the decimal point, it's easy to get rid of them, as explained in the following procedure.)

Format numbers as percentages
https://support.office.com/en-us/article/Format-numbers-as-percentages-de49167b-d603-4450-bcaa-31fba6c7b6b4?ui=en-US&rs=en-US&ad=US&fromAR=1
0
Rob HensonFinance AnalystCommented:
@Ryan - I think I am missing your point. I don't see any mention of formatting empty cells in previous comments. Your comment

42089794

mentions about entering numbers in cells already formatted but that contradicts what the quote from MS says.

Just tested both scenarios:
1) General formatting, enter 0.9 and then format as percentage - result is 90%
2) Format as percentage and then enter 0.9 - same result.

For numbers less than 1 they have to be entered with a percentage symbol or as the true decimal figure.
0
Alistair_MairAuthor Commented:
Thanks guys, I did realise (by attempting a few scenarios) prior to Rob's post that I needed to put a 0 before the decimal point but thank you Rob for taking the time to explain this. Thank you both (Especially Ryan) and I hope you are happy with the point split.   Thanks again Ali
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.