How do you provide points based on a defined lookup targets table

[jpskiller]

Hi

I am trying to find a generic way to assign points based on value results, ie I have a columns labeled Test and Test2 with values, and desired results is the Pts in next column which are based on the lookup table (targets)

Need to lookup by col name in lookup table and then value and incorporate the criteria from lookup table within formula, so if I wanted to change a >= to > this would be automatically handled without hardcoding in the formula, and of course it applies the points from the pts column

I have tried to use hardcode sumifs with "test" and hardcode the criteria and pts, which does work to a point, but its not generic, and would like to have a generic formula so if I applied to another col it would use the col name to lookup against whats in the lookup table

This dosent fully work as also need to be bases on key in col A which gives different points based on that aswell.

And icing on cake would be conditional formatting to work on the column data and points

Thanks in advance for any help

[TMS]

1. Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

2. Are you still using 2010? If not, please update your profile. It can make a big difference in the solutions offered.

[jpskiller]

Cheers, I have now added my attempt example!

[windknife]

Try this in C4

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter and copied down, then copied C4:C13 to G4:G13.

[jpskiller]

Try this in C4

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter and copied down, then copied C4:C13 to G4:G13.

Wow brilliant, that was quick and gave exactly what I wanted, I then added a new Target range and Col called Test3 where I changed the target types from <= to >= and for some reason it doesn't give the correct results, Im not gonna lie I don't fully understand that formula but all the ranges seem to be covered, I uploaded worksheet with the formula in

[HansDouwe]

Another solution:

The whole column in one go:

Please empty cells D4:D13 and H4:H13 and try in D4 and copy to H4:

Please Login or Register  to view this content.

[HansDouwe]

where I changed the target types from <= to >= and for some reason it doesn't give the correct results

Both my formula and Windknife's formula do not take this situation into account, because it was not included in your test set.
It must be clear which degrees of freedom are possible.

I don't have time today, but will make a solution for this tomorrow.
I already know how I want to handle that.

[jpskiller]

Both my formula and Windknife's formula do not take this situation into account, because it was not included in your test set.
It must be clear which degrees of freedom are possible.

I don't have time today, but will make a solution for this tomorrow.
I already know how I want to handle that.

Apologies I wasn't clear about that, really appreciate help so far and must be honest while both formulas are complex, I can follow yours a little more, but I can see what ever is the final working solution is going to require me to do a lot of research understand, which is a good thing

[windknife]

Amended formula in C4

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter

[HansDouwe]

both formulas are complex

The main reason for this is that you allow several comparison signs at the same time in a table (being a unique combination of test and area).

This formula works well when the table (being a unique combination of test and area) contains 1 or more times the comparison sign >= in combination with 0 or 1 time <.
And the formula also works well when the table contains 1 or more of the comparison sign <= in combination with 0 or 1 times >.
Even when the table is not sorted properly.

The formula does not work correctly when a table contains more than 2 times the comparison sign > in combination with 0 or 1 time <=.
This table can always be converted to a table with 1 or more times the comparison sign <= in combination with 0 or 1 time >.
The formula also does not work properly if a table contains more than 2 times the comparison sign > in combination with 0 or 1 time <=.
This table can always be converted to a table with 1 or more times the comparison sign >= in combination with 0 or 1 time <.

Please try in E4 and copy to I4 and M4:

Please Login or Register  to view this content.

[jpskiller]

Thank you this looks great

[HansDouwe]

You are Welcome!
Thanks for your feedback, Glad to have helped.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

[jpskiller]

@HansDouwe

Hi,

2 very different solutions, which is great and will require some study to understand as currently beyond my ability

I added more targets so targets went up to row 33, but when I try and change formula that reference targets row 25 to 33 I get error 'you cannot change part of an array' any idea why that is

[HansDouwe]

My formula isn't written very neatly yet.
When you adjust the range of the table, you have to do that in 6 places. [Wherever it says 25 now].
Late tonight.I will upload a formula in which you only have to indicate the range of the table once.
If another member has not done so yet.

[ExceLogan]

hi everyone, after check the consult I allow to propose another possible solution:

Please Login or Register  to view this content.

This formula spill all the results and permits to change the number of "Test" in cell B3 in order to obtain the results of G4:G13 in the same column.

I hope it results useful.

[AliGW]

@ExcelLogan

The functions are not in English. See if you can use this to translate the formula, please: https://en.excel-translator.de/translator/

It would be helpful to attach a workbook, as the formula will be translated there by Excel.

The lingua franca here is English for posts and formulae.

Thank you.

[TMS]

I usually do the row adjustment like this:

Please Login or Register  to view this content.

assuming there are no blank cells in column D. Pick any fully populated column or use MATCH.

[jpskiller]

@ExceLogan

Are you able to upload workbook with this in so Excel can do the translation please

[AliGW]

I had already asked for this in post #16, but I have also contacted ExcelLogan privately to ask them to attach a workbook here.

[jpskiller]

Sorry I missed that, now I have seen, and I tried that translate link you provided but could not get results working

[AliGW]

No, it's missing TOMAR, which is TAKE, and EXCLUIR - I don't know which function that should be.

Please don't quote unnecessarily. Thanks.

=MAP(A4:A13,B4:B13,LAMBDA(a,t,LET(_at,Targets!A2:A17&Targets!B2:B17,_p,XMATCH(a&B3,_at,,-1),_m,EXCLUIR(TAKE(Targets!C2:E17,_p),_p-SUM(--(_at=a&B3))),_Pts,INDEX(_m,,3),IFNA(INDEX(_Pts,XMATCH(10,SUMIF(t,INDEX(_m,,2)&INDEX(_m,,1),_Pts))),))))

[AliGW]

I've worked it out - EXCLUIR is DROP:

=MAP(A4:A13,B4:B13,LAMBDA(a,t,LET(_at,Targets!A2:A17&Targets!B2:B17,_p,XMATCH(a&B3,_at,,-1),_m,DROP(TAKE(Targets!C2:E17,_p),_p-SUM(--(_at=a&B3))),_Pts,INDEX(_m,,3),IFNA(INDEX(_Pts,XMATCH(10,SUMIF(t,INDEX(_m,,2)&INDEX(_m,,1),_Pts))),))))

[jpskiller]

Thanks, have given it a try but just get #NAME?, be great to see in a working example

[AliGW]

If you have the correct version of Excel, you won't get the name error. Works for me.

Attached so that you can double-check.

[jpskiller]

I see, current laptop only has 2016 on, other laptop that has 365 on out of order at min, will try it tomorrow when working

[AliGW]

That'll be it, then.

[ExceLogan]

Hi again, my apologies for the miss-translation in my proposal.

I already changed and translate my post, if someone need to verify it.

[AliGW]

Too late - I've already done it for you (post #22).

In future, please always provide workbooks as well as formulae. Thanks.

[HansDouwe]

In this formula the ranges are defined 1 time at the beginning of the formula, so its easier to adjust the formula, if there are other ranges:

The range with the tables could be oversized.

Please Login or Register  to view this content.

[jpskiller]

Hi All,

I would like to thank all who have been involved I have managed to fully test and use @windknife version, I like this as its easier to digest and is also backward compatible.

I have not fully tested the others yet, had a play with @HansDouwe version which so far seems to work but to me is very complex, I also tried @ExcelLogan version but this seemed to fail on the test 3 variation I posted in 2nd upload.

However all help if much appreciated.

[AliGW]

Glad to have helped.

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

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 all those who offered help.

[ExceLogan]

hi everyone, I share again my solution proposal that I tested and check to results was correct for the three tests.

I hope it results useful.

Points based on Value (Solution).xlsx

[jpskiller]

Thank you I will give this further testing over next few days and feedback results

[jpskiller]

@ExcelLogan

I have tested your version and it does work well, I have 1 point, if I was to extend the range so it exceeds what's being targeted,
i.e. to cover auto data growth, how do you prevent it from putting 0 where either column A is blank
or the column begin used to target is blank.

I have attached example

Thanks

[AliGW]

This:

=MAP($A$4:$A$100,$B$4:$B$100,LAMBDA(a,t,LET(_at,Targets!$A$2:$A$33&Targets!$B$2:$B$33,_p,XMATCH(a&$B$3,_at,,-1),_m,DROP(TAKE(Targets!$C$2:$E$33,_p),_p-SUM(--(_at=a&$B$3))),_Pts,INDEX(_m,,3),IFNA(INDEX(_Pts,XMATCH(10,SUMIF(t,INDEX(_m,,2)&INDEX(_m,,1),_Pts))),""))))

[jpskiller]

Thank you, I don't seem to be able to give any reputation to @ExcelLogan, I have been able to with others and yourself

[AliGW]

Are you getting a message telling you to spread the rep before repping him again? If so, there's your answer. It's a security measure to prevent people from mutually repping each other to artificially increase their rep count (yes, it has happened!).

[jpskiller]

Cheers, sorted now