HLOOKUP with multiple criteria

[JCTalk]

Hi again guys,

I'm working on a scoring system for my fellow call centre agents. I've obtained percentage work completed (e.g. of calls taken) for part of the scoring system, and now need to lookup from a threshold table.

I believe I need to use an HLOOKUP that checks three criteria, but I've had no experience with these, only VLOOKUPS. I've looked around and INDEX & MATCH options often get mentioned for efficiency, but again I've not been able to wire it up to produce the results I need.

My criteria is Team (e.g. Team 1), Contract (e.g. Full Time) and Points (e.g. 0). The top table is the main scoring table, the table below it is the lookup table. In the threshold table, 0 points is considered the expected work completed (i.e. if they only do the minimum that's expected).

What I need is:

1. The shortest most efficient formula to lookup e.g. in the instance of the first data row of the scoring table, the percentage thresholds using criteria cell's C3 and B3. I need to do this for each of the scores in column A of the lookup table.

The purpose of the above is to enable me to build an IF formula in column's E & G to compare the actual agent percentage in the top table, to see where it sits in the lookup table and assign the appropriate points from column A of the lookup table. I'm confident I can wire up the IF's to check greater or less than the percentages in thresholds and assign the appropriate points, I just need to know how to lookup those thresholds first so I can compare them.

Spreadsheet attached for your viewing pleasure.

Please can you provide me assistance in obtaining the above guys. Many thanks in advance for any help you can offer me.

 

[NARAYANK991]

Hi ,

It would help if you could put in manually the outputs that would appear in cells E3 through E7 and G3 through G7. I assume that these are the cells where you want formulae.

Narayan

 

[JCTalk]

It would help if you could put in manually the outputs that would appear in cells E3 through E7 and G3 through G7. I assume that these are the cells where you want formulae.

Hi Narayan,

Many thanks for your quick reply.

The yellow cells I highlighted will be where my IF formula goes to do the comparison, which I have yet to build. My apologies highlighting them does confuse things.

At this stage, all I need is the INDEX & MATCH formula to return the percentage in the bottom lookup table based on Team name in e.g. C3, Contract in e.g. B3 and Points in e.g. A16.

So as an example, the above formula should return the result... 4.00%

Many thanks Narayan.

 

[JCTalk]

Hi Narayan,

My apologies, I now understand what you meant. Sorry. I have populated the highlighted cells with what I would expect after I have done the comparison using the lookups. Is that what you meant?

I've deleted the Other Calls column as it was just confusing lol.

Many thanks

 

[NARAYANK991]

Hi ,

Sorry but I am still confused ; if I take the following inputs :

B3 : Full Time
C3 : Team 1

Together , these two mean I should look up some value in the range B12:B20 , since that is the range corresponding to Full Time and Team 1.

But what is the value to be looked up ?

How do you say the output of the formula should be 4.00 % ?

Can you explain ?

Narayan

 

[NARAYANK991]

Hi ,

Thank you ; now everything is clear.

Narayan

 

[JCTalk]

Many thanks Narayan.

I think the confusing bit was that I was mentioning two things, rather than focusing on what I actually needed lol.

Thank you.

 

[NARAYANK991]

Hi ,

See your file ; I have used a helper column G to get the output in H ; the output in I is without the helper column. See if the logic is correct.

Narayan

 

[JCTalk]

Hi Narayan,

I've checked over the formula. The logic seems to work great. I changed a few scores and it handled them fine. I prefer the single column I calculation.

One concern though. In the formula it is hard coded "Team 1" and "Full Time". Lets assume I am adding more Teams in and more contract types, how do I reflect that in the formula?

Is it possible to provide an INDEX & MATCH formula to get the percent back from the threshold table as well?

Main reason I was looking that way originally was because I hoping to understand the lookup formula so I could alter it as necessary to cope for other criteria that I may need to look up in the future as well.

So as my example earlier, the INDEX & MATCH formula to return the percentage from the bottom lookup table based on criteria cell reference $C3 (team name), cell reference $B3 (contract type) and $A16 (Points).
The above example formula should return the result... 4.00%

Thank you Narayan.

 

[NARAYANK991]

Hi ,

The use of merged cells , as well as the data layout of the lookup table , makes it all so complicated.

See the attached file.

Narayan

 

[JCTalk]

The use of merged cells, as well as the data layout of the lookup table, makes it all so complicated.

Hi Narayan,

Many thanks. I understand the formula in the column you labelled "Output", but the formula in the helper column looks very complex to manage.

You mention the use of merged cells and the data layout of the lookup table being complicated. Is there a better way that I could set this out that would enable us to use an easier lookup method that I could maintain easier?

See attached two additional layouts for the lookup table. Do they make it easier?
The top orange table I have just removed the merged cells and repeated the data. How would an INDEX & MATCH formula work on that?

 

[NARAYANK991]

Hi ,

See this file for both options.

Narayan

 

[JCTalk]

Hi Narayan,

Wow. Just wow. Lol. That is some intense Excel.

I love the fact that table 2 can operate from one field only, without making use of the named ranges or the helper column. Could the table 1 ever work like this from the one cell without needing the offset ranges and a helper column, or is it due to the horizontal layout that it can't?

I notice the use of "|" in table 2's calculation. Do you mind if I ask what they do? I've not seen them used in a formula calculation before.

Thank you Narayan.

 

[NARAYANK991]

Hi ,

The problem with the first table is the mixed orientation ; the teams and the contract types are laid out horizontally , while the data itself is in columns.

In the second table , everything is columnar , which makes it easy to use the concatenation technique , though on large data sets this might be quite slow.

The "|" character is not necessary in this case , but I use it as a standard , nothing more. Suppose you are concatenating two numbers , where one number is 11 and the other number is 237 ; without the special character ( it can be any character which does not occur in the data set ) , this would result in the concatenated string 11237.

Now , within your data set , you might have an occurrence where the number 11 and 237 is a perfect match ; but it can also happen that you have the numbers 112 and 37 , which when concatenated would also result in 11237 , thus matching the looked for value.

By putting a special character in between the two values , we now look for the string 11|237 , which would match only with this combination , and not with 112|37

Narayan

 

[JCTalk]

Many thanks for your informative reply Narayan. Thank you again for helping me out with this situation. I am sincerely grateful as ever. I could not have resolved this without your help. Thank you.

 

[JCTalk]

Hi Narayan,

I've found a problem in the logic of the calculation I wonder if you might please be able to look at?

When the workload is even lower than the lowest threshold (the one that gives -10 points). Instead of giving -10, it seems to default back up to 0.

The logic should be if its higher than the highest threshold give 10 points (which it seems to do), if its lower than the lowest threshold give -10.

Can you please help with that Narayan?

Thank you.

 

[NARAYANK991]

Hi ,

Sorry. See this file.

Narayan

 

[JCTalk]

Hi Narayan,

Many thanks. I have taken to using the vertical table for the purposes of keeping things simple. Someone else will inevitably have to take control of this after I'm done with it - they won't have even my basic understanding of Excel lol.

Thank you again my friend.

 

[JCTalk]

Hi Narayan,

Sorry, but I seem to have a problem with this when the lookup value changes from a percentage to a time.

I've amended the file with what I would expect to be returned, but it seems it will only ever bring back 10 or -10 strangely.

Thanks Narayan.

 

[NARAYANK991]

Hi ,

That is to be expected.

If you convert the format of the range in column D to General , you can see the decimal values for the times as well as the percentages.

In the case of the percentages , your data is arranged in descending order , with the highest percentage having the highest score or 10 , while the lowest percentage has the lowest score of -10.

In the case of the times , it is the other way around.

Narayan

 

[JCTalk]

Ah right I see. So is there a formula change I can make to correct this for situations when a time and a different order is used?

I tried changing the >= to a <=, but that didn't seem to make any difference other than change the -10 to a 10.

Many thanks

 

[NARAYANK991]

Hi ,

See this file ; you can enter either a time or a percentage , but the change to a percentage will take effect only if the cell format is changed to percentage.

Narayan

 

[JCTalk]

Many thanks Narayan. That seems to work well and as planned for scoring 0>10, but for 0>-10 it seems to pick the wrong one.

On your previous attachment, example score of 07:41 currently gives -5. As the time hasn't yet reached the threshold for -5 (07:42) the points returned should be -2.

In addition, I must admit the formula is breaking my mind a bit as to how it achieves what it does haha.

• I understand the cell check to find out if its a percentage, but what is the "P2" reference? As its in "" its a text string right? Where is it referencing?
• I also see reference to 999. What is that part doing?

I've tried to map it out to follow the logic but I'm struggling. Do you have time at all to walk me through the formula Narayan as I'd like to understand and learn for the future?

Thank you as always for your help Narayan.

 

[NARAYANK991]

Hi ,

Hopefully this should work. Try it out thoroughly ; once you confirm that it works in all situations , I will explain fully.

Narayan

 

[JCTalk]

Hi Narayan,

Unfortunately it seems to have gone the other way. Now it will function correctly between 0>-10, but when the value is between 0>10 it chooses the wrong value. :-(

Example time 06:59. The threshold is 07:00 for 0 points, and 06:39 for 2 points. The logic here is it should choose 0 as it is lower than 07:00 but higher than 06:39.

Many thanks