Index match multiple criteria

[kripper]

Been a while since I posted.....have had a lot of success with tips from the amazing people here.....but now I am stumped, can't seem to get this formula to pull the results I need, just get an #NA error.

I have a two part question if I may.....

{=INDEX('Q1'!$A:$A,MATCH(1,INDEX(('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0,1),0))}

On the data sheet is a serial number, column c, is an employee number and column BI is a date value column identifying the date of the call, and I am trying to get the formula to reference the serial number between the two dates in question using the employee number as reference.

Then on the next column, I want to pull the next serial number for the same employee in the same date range.....using the second formula..

{=INDEX('Q1'!$A:$A,MATCH(1,INDEX(('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35)*(COUNTIF($ZY$3:ZY3,'Q1'!$A$2:$A$9630),0,1),0))}

Hoping someone can help point out what I am missing.......

 

[cerfani]

i just looked quickly so i could be wrong... but you have an index function inside the match function... i dont think you mean to do that...

{=INDEX('Q1'!$A:$A,MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0))}

you only want match to return row numbers of matches for 1

 

[kripper]

Thanks for the reply.

I have tried multiple different ways, and cannot seem to get it work correctly, I still receive and #NA error when using the formula provided.

I am sure there is a way to reference it back to using the DATEVALUE as I can us it the same way on another sheet using SUMIFS

 

[cerfani]

well i can explain why this might be wrong... with match you are looking to return the first result from an array that matches 1 (in your case you are testing 3 conditions and all 3 must be true for the array position to be set at 1) and to give it's position in the array which you want to correspond to the position in column A

[COLOR=#333333]MATCH(1,INDEX(('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0,1),0)[/COLOR]

that's what you have inside the first index function to retrun the row you want to reference in column A but index simply returns a single value... you are telling it to go to row 0 column 1 of the...

('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35)

....array. So that doesnt make sense... the array that formula builds is going to be 1048576 rows by 1 column so telling it to go to row 0 and column 1 is wrong... which is why i suggested removing that index so you can instead reference the position of the first match from that array. i hope that makes sense and maybe you can see now why your starting formula looked wrong.

 

[kripper]

Thanks again, however modifications to correct or are still giving me an #NA error.....maybe it's because I have been up for 36+ hours, but normally I can see where I messed up....

 

[cerfani]

well without the file i will go through your formula...

On the data sheet is a serial number, column c, is an employee number and column BI is a date value column identifying the date of the call

so to get the row number on the Q sheet that matches this criteria, you do...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)}

that will give you the first row number where all 3 of those formulas are true

if the serial number is also on sheet Q and in the corresponding column A row... then to get the serial number from your match you do...

{=INDEX('Q1'!$A:$A,MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0))}

that will give you the serial number you want... as long as your data is setup as you described.

your 2nd formula i would actually try to use the indirect function to create an array that excludes the first match...

so your first match is on row...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)}

so I would add 1 to that and start searching from there, maybe indirect function can let you use the first formula again but with dynamic ranges since you need to calculate the row of the first match and add 1 to it

dynamic range example...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)+1}

that gets the next row after first match... for example i will say this value is in address XYZ1

then your ranges in your formula need to be edited to use indirect...

like for the C column, instead of...

'Q1'!$C:$C

use... (remember the value for the next row after first match i put in xyz1)

INDIRECT("Q1!$C"&XYZ1&":$C1048576")

(1048576 is the last row of column C... you should shorten to actual length of table if you know it)

 

[kripper]

well without the file i will go through your formula...

so to get the row number on the Q sheet that matches this criteria, you do...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)}

that will give you the first row number where all 3 of those formulas are true

if the serial number is also on sheet Q and in the corresponding column A row... then to get the serial number from your match you do...

{=INDEX('Q1'!$A:$A,MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0))}

that will give you the serial number you want... as long as your data is setup as you described.

your 2nd formula i would actually try to use the indirect function to create an array that excludes the first match...

so your first match is on row...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)}

so I would add 1 to that and start searching from there, maybe indirect function can let you use the first formula again but with dynamic ranges since you need to calculate the row of the first match and add 1 to it

dynamic range example...

{=MATCH(1,('Q1'!$C:$C=$D3)*('Q1'!$BI:$BI>=AAA$35)*('Q1'!$BI:$BI<=$AAB$35),0)+1}

that gets the next row after first match... for example i will say this value is in address XYZ1

then your ranges in your formula need to be edited to use indirect...

like for the C column, instead of...

'Q1'!$C:$C

use... (remember the value for the next row after first match i put in xyz1)

INDIRECT("Q1!$C"&XYZ1&":$C1048576")

(1048576 is the last row of column C... you should shorten to actual length of table if you know it)

Unfortunately still get an #NA error.

I have redone that dates formulas to ensure they were displaying as datevalue properly, and when I perform a SUMIFS on a helper column, I get the totals between the ranges, so I know that is working......

Any other suggestions would be appreciated.

 

[cerfani]

well i think for me to go further i would need the actual file and i would try to do it and debug the issue

first thing I would do is try to evaluate the function... you are going to get some giant arrays popping up in that tiny little window that cant be resized (because they create an image of the formula instead of it being text...why MS? why?)

but it is still usable or you can highlight different parts of the function in the formula and press F9 to resolve the highlighted parts... so you can in a way step through each operation of the formula directly in the formula bar which can be resized. You need to identify where things go haywire. I am thinking there is something I might not be aware of regarding your table because index match with 3 test conditions is pretty straightforward

 

[kripper]

well i think for me to go further i would need the actual file and i would try to do it and debug the issue

first thing I would do is try to evaluate the function... you are going to get some giant arrays popping up in that tiny little window that cant be resized (because they create an image of the formula instead of it being text...why MS? why?)

but it is still usable or you can highlight different parts of the function in the formula and press F9 to resolve the highlighted parts... so you can in a way step through each operation of the formula directly in the formula bar which can be resized. You need to identify where things go haywire. I am thinking there is something I might not be aware of regarding your table because index match with 3 test conditions is pretty straightforward

Wasn't able to send the whole file, so I copied the formulas from the sheets I am having issues with.

https://1drv.ms/x/s!Amk_7FQHP36kgv9ERJWLtZvejnL-Aw

 

[cerfani]

in the DATA 1 column, the formulas are not entered as array formulas... use Ctrl+Shift+Enter

also your formula for checking between dates is not testing if the value on Q1 is between the dates but you are testing the date in column F is greater than the date in Q1!BG and the date in column G is less than the value in Q1!BG.

I think you mean to test if the value in BG is between the dates in columns F and G so...

{=INDEX('Q1'!$A$2:$A$50000,MATCH(1,('Q1'!$B$2:$B$50000=A2)*('Q1'!$BG$2:$BG$50000>=F2)*('Q1'!$BG$2:$BG$50000<=G2),0))}

make sure you enter that formula with Ctrl+Shift+Enter

when i downloaded the file, in DATA!B2 you wrote...

[COLOR=#ff0000]=[/COLOR]INDEX('Q1'!$A$2:$A$50000,MATCH(1,('Q1'!$B$2:$B$50000=A2)*([COLOR=#ff0000]F2>='Q1'!$BG$2:$BG$50000[/COLOR])*([COLOR=#ff0000]G2<='Q1'!$BG$2:$BG$50000[/COLOR]),0))

and it was not entered as an array