Index Match multiple results skip the blanks

[rdbauer83]

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!

 

[rdbauer83]

Could we have a small sample data where that is the case so that I can investigate?

I actually stated playing around and it will have the name below the time instead of in column S...IF there is only 1 person scheduled for that day. As soon as I changed the date where 2 ore more people are working that area (Q) then it was correct all around

 

[rdbauer83]

I actually stated playing around and it will have the name below the time instead of in column S...IF there is only 1 person scheduled for that day. As soon as I changed the date where 2 ore more people are working that area (Q) then it was correct all around

if there is a work around to where it will perform the same way if only 1 person is scheduled for that date and area (column Q) then great, if not I can make it work with some helper columns to get what I want the final product to be you are awesome and have been a tremendous help already

 

[Peter_SSs]

if there is a work around to where it will perform the same way if only 1 person is scheduled for that date and area (column Q) then great,

You are familiar with your data and requirements, but we are not. Please give some small sample data that demonstrates the issue(s) you are describing and describe again in relation to that sample data what is wrong with the results currently and what result(s) you would want instead.

 

[rdbauer83]

You are familiar with your data and requirements, but we are not. Please give some small sample data that demonstrates the issue(s) you are describing and describe again in relation to that sample data what is wrong with the results currently and what result(s) you would want instead.

Ok, IF sheet 2 only has one person scheduled that day and that position...

Book1
	B	C	D	E	F	G	H	I	J
4			4/25	4/26	4/27	4/28	4/29	4/30	5/1
5
6	HELP	Name 1	a	b	c	d
7	HELP	Name 2				e	f	g	h
8	HELP	Name 3	i				j	k	l
9	HELP	Name 4	m	n		p	q	r	s
10	HELP	Name 5		t		v
Sheet2

THEN...

Book1
	Q	R	S
2		4/27
3		TIME	NAME
4
5
6
7	HELP	c
8	HELP	Name1
9	HELP
10	HELP
11	HELP
Sheet1

But if there are at least 2 people scheduled that day then it is correct...

Book1
	B	C	D	E	F	G	H	I	J
4			4/25	4/26	4/27	4/28	4/29	4/30	5/1
5
6	HELP	Name 1	a	b	c	d
7	HELP	Name 2				e	f	g	h
8	HELP	Name 3	i				j	k	l
9	HELP	Name 4	m	n	o	p	q	r	s
10	HELP	Name 5		t	u	v
Sheet2

Book1
	Q	R	S
2		4/29
3		TIME	NAME
4
5
6
7	HELP	f	Name 2
8	HELP	j	Name 3
9	HELP	q	Name 4
10	HELP
11	HELP
Sheet1

 

[Peter_SSs]

Thanks, that makes it much easier to understand.

Try this in R7, copied down, instead.

=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),0),""))

 

[rdbauer83]

Thanks, that makes it much easier to understand.

Try this in R7, copied down, instead.

=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),0),""))

THAT IS IT!!!!! PERFECT thank you so much

 

[Peter_SSs]

You're welcome. Glad we got there in the end.