Range Lookup w/multiple criteria

[lfcreds11]

Hi all,
I have the table below on a separate tab on my worksheet. I would like to write a formula in a column on another sheet that basically would say:

If the calls (column A) are between 0-500 and the day is "Normal" (column B) then find the matching # which in this example is 15 (B2).

I would like to use this formula to lookup for all of the listed # of calls and day (normal through L3).

Thanks in advance!

#Calls	Normal	Ops	L1	L2	L3
0-500	15	15	15	30	30
501-800	15	15	30	30	30
801-1000	30	30	30	60	60
1001-1200	60	30	60	60	90
1201-1400	60	60	60	90	90
1401-2000	90	90	90	90	90

 

[jasonb75]

First of all, delete the upper limits from the #Calls column in the table that you show above. i.e. the first one should just be 0 not 0-500, the second should just be 501, etc.

Then, assuming that the table above is in A1:F8 of Sheet2, with the Calls and Day to look for in A2 and B2 of Sheet1, your formula would be

=IFERROR(INDEX(Sheet2!$B$2:$F$8,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$8),MATCH(Sheet1!$B2:Sheet1!$B$1:$F$1,0)),"No Match")

 

[lfcreds11]

thank you I should have added a bit more info. Sheet 1 is called Stats, Sheet 2 is called Matrix. The data in the Stats sheet for calls is in column M and the data for the day status is in column R

 

[Aladin Akyurek]

Does the specification 0-500 occurs always as "0-500" if it occurs at all in column M of Stats?

 

[lfcreds11]

No everyday is a different number. So we are trying to say if the number of calls is in the range between 0 and 500 and the Day Status is Normal then the matrix is 15. If it is between 1001 and 1200 calls and the Day status is L3 then the matrix is 90 etc. Thanks for trying to assist I appreciate it!

 

[jasonb75]

Sheet 1 is called Stats, Sheet 2 is called Matrix. The data in the Stats sheet for calls is in column M and the data for the day status is in column R

I did just notice a typo in the formula, but aside from that, all you need to do is change the formula to match what you actually have

=IFERROR(INDEX(Sheet2!$B$2:$F$8,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$8),MATCH(Sheet1!$B2,Sheet2!$B$1:$F$1,0)),"No Match")

=IFERROR(INDEX(Matrix!$B$2:$F$8,MATCH($M2,Matrix!$A$2:$A$8),MATCH($R2,Matrix!$B$1:$F$1,0)),"No Match")

You don't actually need to enter STATS as a sheet name because the formula is going into that sheet anyway, I just added it in for easier identification.
You do still need to change the way that the #Calls are entered on the stats sheet as I advised in post 2 though.

 

[lfcreds11]

I did just notice a typo in the formula, but aside from that, all you need to do is change the formula to match what you actually have

=IFERROR(INDEX(Sheet2!$B$2:$F$8,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$8),MATCH(Sheet1!$B2,Sheet2!$B$1:$F$1,0)),"No Match")

=IFERROR(INDEX(Matrix!$B$2:$F$8,MATCH($M2,Matrix!$A$2:$A$8),MATCH($R2,Matrix!$B$1:$F$1,0)),"No Match")

You don't actually need to enter STATS as a sheet name because the formula is going into that sheet anyway, I just added it in for easier identification.
You do still need to change the way that the #Calls are entered on the stats sheet as I advised in post 2 though.

Thank you I did change the numbers as you advised to show 0, 501, 801 etc. I am still getting no match. It should be looking at the number of calls that fall into the range, for example a volume of 350 calls should fall within the 0-500 which we renamed to 0 on the chart. Is this formula looking for an exact match? I am sorry for all the questions I am still new at this.

 

[jasonb75]

If everything is looking in the right place then the formula should give you the result that you want.

Process of elimination, do either of these return a number?

=MATCH($M2,Matrix!$A$2:$A$8))

=MATCH($R2,Matrix!$B$1:$F$1,0))

 

[lfcreds11]

yes this one returns the number 3. =MATCH($M2,Matrix!$A$2:$A$8))

 

[jasonb75]

That would mean that the number of calls in M2 falls into the 801-1000 range.

If the other formula returned #N/A! then that means that the day reference in R2 can not be matched to anything in B1:F1 of the matrix. Are both ranges correct? Is R2 an exact match for one of the entries in B1:F1 of the matrix (check for extra spaces)?