Index Match multiple results skip the blanks

rdbauer83

New Member
Joined
May 2, 2022
Messages
14
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. Windows
Hi there hope someone can help as I am about to lose it because I know there is something simple. I have a table and I need another sheet to return only cells that match 2 given criteria and aren't empty. I have 2 worksheets and need the information from worksheet 2 to show up on worksheet 1 with given criteria and only cells from worksheet 2 that have information. I have uploaded worksheet 1 without the info, worksheet 2 that has the information needed, and final result which is what I need worksheet 1 to look like. Thank you all for the help!
Worksheet 1.jpg
Worksheet 2.jpg
Final Result.jpg
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the MrExcel board!

For the future, please consider the following so that we can easily copy/paste your sample data to test with as most helpers are not that keen on manually typing sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Assuming the values in R2 of Sheet1 and row 4 of Sheet2 are actual dates (formatted to show month/day only) then with Microsoft 365 (or 2021) try this.

rdbauer83.xlsm
BCDEFGHIJ
44/254/264/274/284/294/305/1
5
6HELPName 1abcd
7HELPName 2efgh
8HELPName 3ijkl
9HELPName 4mnopqrs
10HELPName 5
Sheet2



rdbauer83.xlsm
QRS
24/28
3TIMENAME
4
5
6
7HELPdName 1
8HELPeName 2
9HELPpName 4
10
Sheet1
Cell Formulas
RangeFormula
Q7:S9Q7=LET(col,INDEX(Sheet2!D6:J10,0,MATCH(R2,Sheet2!D4:J4,0)),FILTER(CHOOSE({1,2,3},Sheet2!B6:B10,col,Sheet2!C6:C10),col<>""))
Dynamic array formulas.
 
Upvote 0
Thank you for the reply. I apologize for the formatting as this is the first time I have posted on a board. That did do what I wanted, now I have different categories and have a different number of people for each on any given day with different times. With that, what is a solution for this? Thank you again
Sheet 1
Book1
QRS
24/28
3TIMENAME
4
5
6
7HELP
8HELP
9HELP
10HELP
11HELP
12ALPHA
13ALPHA
14ALPHA
15ALPHA
16ALPHA
17BRAVO
18BRAVO
19BRAVO
20BRAVO
21BRAVO
Sheet1


Sheet 2
Book1
BCDEFGHIJ
44/254/264/274/284/294/305/1
5
6HELPName 1abcd
7HELPName 2efgh
8HELPName 3ijkl
9HELPName 4mnopqrs
10HELPName 5tuv
11ALPHAName 6wxyz
12ALPHAName 7ABCD
13ALPHAName 8EFGH
14ALPHAName 9IJKL
15ALPHAName 10MNOP
16ALPHAName 11QRST
17ALPHAName 12UVWX
18ALPHAName 13YZAABB
19BRAVOName 18CCDDEEFF
20BRAVOName 19GGHHIIJJ
21BRAVOName 20KKLLMMNN
22BRAVOName 21OOPPQQRR
23BRAVOName 22SSTTUUVV
24BRAVOName 23WWXXYYZZ
25BRAVOName 24AAABBBCCCDDD
26BRAVOName 25EEEFFFGGGHHH
27BRAVOName 26IIIJJJLLL
Sheet2



Final sheet 1 date 4/28
Book1
QRS
24/28
3TIMENAME
4
5
6
7HELPdName 1
8HELPeName 2
9HELPpName 4
10HELPvName 5
11HELPblankblank
12ALPHACName 7
13ALPHAGName 8
14ALPHAMName 10
15ALPHABBName 13
16ALPHAblankblank
17BRAVOFFName 18
18BRAVOGGName 19
19BRAVOUUName 22
20BRAVOZZName 23
21BRAVOAAAName 24
22BRAVOJJJName 26
23BRAVOblankblank
24BRAVOblankblank
Sheet1


Final if date in Sheet1!R2=4/27
Book1
QRS
24/27
3TIMENAME
4
5
6
7HELPcName 1
8HELPoName 4
9HELPuName 5
10HELPblankblank
11HELPblankblank
12ALPHABName 7
13ALPHAJJJName 9
14ALPHASName 11
15ALPHAVName 12
16ALPHAAA Name 13
17BRAVOEEName 18
18BRAVOTTName 22
19BRAVOYYName 23
20BRAVOIIIName 26
21BRAVOblankblank
22BRAVOblankblank
23BRAVOblankblank
24BRAVOblankblank
Sheet1
 
Upvote 0
I don't understand the logic.

For 4/28
For HELP original data has 1 blank row and your expected results has 1 blank row.
For ALPHA original data has 4 blank rows and expected results has 1 blank row.
For BRAVO original data has 3 blank rows and expected results has 2 blank rows.
 
Upvote 0
I don't understand the logic.

For 4/28
For HELP original data has 1 blank row and your expected results has 1 blank row.
For ALPHA original data has 4 blank rows and expected results has 1 blank row.
For BRAVO original data has 3 blank rows and expected results has 2 blank rows.
Sorry, maybe this helps, "Sheet 2" is an overall total staffing sheet with names and shifts that each person will be working for that week. "Sheet 1" is a daily staffing sheet where we won't have every person working everyday in that area but based on the day of the week we will need different levels of staff to work. "HELP", "ALPHA", AND "BRAVO" are the positions, names (self explanatory), and the letters are the shift for that date they will work. On Sheet 1 (depending on the day of the week) we will have a MAX of 5 "HELP", 5 "ALPHA", and 8 "BRAVO" workers, but on a different day we may only need 3-4. This is the first step in a workbook that has many more after and I have steps 2-20 done...I just can't get this first step to work giving me the information needed without typing in each name/shift each day or having to create another worksheet to copy and paste each day to pull data from using vlookups. Sorry hope this helps explain what it is used for
 
Upvote 0
we will have a MAX of 5 "HELP", 5 "ALPHA", and 8 "BRAVO" workers,
Does that mean that Q7:Q24 in Sheet1 is already filled in with those values and your request is for formulas to fill R7:S24 only?
 
Upvote 0
Does that mean that Q7:Q24 in Sheet1 is already filled in with those values and your request is for formulas to fill R7:S24 only?
yes sir that is correct. Column Q has the max number of people per area that would work in a day. I just need the name and times for each area on a given day. Actually there are 90 rows in columns Q-S with possible people to fill 11 different positions on a day, but if I can get this to work then I can transpose to the rest of the sheet :)
 
Upvote 0
yes sir that is correct.
OK, for the setup from post #3 here is my Sheet1

rdbauer83_1.xlsm
QRS
1
24/28
3TIMENAME
4
5
6
7HELPdName 1
8HELPeName 2
9HELPpName 4
10HELPvName 5
11HELP 
12ALPHACName 7
13ALPHAGName 8
14ALPHAMName 10
15ALPHABBName 13
16ALPHA 
17BRAVOFFName 18
18BRAVOGGName 19
19BRAVOUUName 22
20BRAVOZZName 23
21BRAVOAAAName 24
22BRAVOJJJName 26
23BRAVO 
24BRAVO 
Sheet1
Cell Formulas
RangeFormula
R17:S22,R12:S15,R11,R16,R23:R24,R7:S10R7=LET(col,INDEX(Sheet2!D$6:J$27,0,MATCH(R$2,Sheet2!D$4:J$4,0)),IFERROR(INDEX(FILTER(CHOOSE({1,2},col,Sheet2!C$6:C$27),(col<>"")*(Sheet2!B$6:B$27=Q7)),COUNTIF(Q$7:Q7,Q7)),""))
Dynamic array formulas.
 
Upvote 0
OK, for the setup from post #3 here is my Sheet1

rdbauer83_1.xlsm
QRS
1
24/28
3TIMENAME
4
5
6
7HELPdName 1
8HELPeName 2
9HELPpName 4
10HELPvName 5
11HELP 
12ALPHACName 7
13ALPHAGName 8
14ALPHAMName 10
15ALPHABBName 13
16ALPHA 
17BRAVOFFName 18
18BRAVOGGName 19
19BRAVOUUName 22
20BRAVOZZName 23
21BRAVOAAAName 24
22BRAVOJJJName 26
23BRAVO 
24BRAVO 
Sheet1
Cell Formulas
RangeFormula
R17:S22,R12:S15,R11,R16,R23:R24,R7:S10R7=LET(col,INDEX(Sheet2!D$6:J$27,0,MATCH(R$2,Sheet2!D$4:J$4,0)),IFERROR(INDEX(FILTER(CHOOSE({1,2},col,Sheet2!C$6:C$27),(col<>"")*(Sheet2!B$6:B$27=Q7)),COUNTIF(Q$7:Q7,Q7)),""))
Dynamic array formulas.
Yes that is what I needed...except on some of the rows the name is appearing below the time instead of in column "S". When I change the date some will be correct and others will not
 
Upvote 0
Could we have a small sample data where that is the case so that I can investigate?
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top