VLOOKUP - Need formula?

[Kapz786]

Hi Guys

Looking to use a formula - probably VLOOKUP - My sheat is a much larger version of the below table that lists employee's first name, surname and then all the dates for a year. I then enter if someone is "A" for "Available", AM for "Available AM only" or PM for "Available PM only".

What I want to do is have a formula where I can just enter a date such as 04-07 below and it will display the 2 employee's who have listed any of A, AM or PM. Would be great if it can be done with both first name and surname (they are on 2 different columns)

First Name	Surname		01-07	02-07	03-07	04-07	05-07	06-07	07-07
John	Smith			A		AM		PM	A
Sam	Black			PM
Bill	White		A			A	PM
James	Blue				AM

<tbody>
</tbody>

 

[Kapz786]

Sorry I should say I need it to list all the employee's which could be up to 20 or so - in this example for 04-07 it is just 2 persons

 

[FormR]

Hi, welcome to the forum.

See if you can adapt this to your set-up. The formula in L2 is a helper formula to aid efficiency - the formulas in M2 and N2 can be copied down as required.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	First Name	Surname	01-Jul	02-Jul	03-Jul	04-Jul	05-Jul	06-Jul	07-Jul		Date	Helper	1st Name	Surname
2	John	Smith		A		AM		PM	A		04-Jul	2	John	Smith
3	Sam	Black		PM									Bill	White
4	Bill	White	A			A	PM
5	James	Blue			AM
Sheet1

Cell Formulas
Range	Formula
L2	=SUM(COUNTIF(INDEX($C$2:$I$1000,0,MATCH(K2,C1:I1,0)),{"A","AM","PM"}))
M2	=IF(ROWS(M$2:M2)>$L$2,"",INDEX($A$2:$A$1000,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/ISNUMBER(MATCH(INDEX($C$2:$I$1000,0,MATCH($K$2,$C$1:$I$1,0)),{"AM","PM","A"},0)),ROWS(M$2:M2))))
N2	=IF(ROWS(N$2:N2)>$L$2,"",INDEX($B$2:$B$1000,AGGREGATE(15,6,(ROW($B$2:$B$1000)-ROW($B$2)+1)/ISNUMBER(MATCH(INDEX($C$2:$I$1000,0,MATCH($K$2,$C$1:$I$1,0)),{"AM","PM","A"},0)),ROWS(N$2:N2))))

 

[Mike Szczesny]

Can you use something like this? Assume your data range (including headings) is A1:I5. Your formula for quantity is in C8. Your formula for Name is in D8. Your formula for surname is in E8. Make sure you take off the $ for the Column, so you can copy across and down to get your additional name and surname. Also copy down from E8 to get your next surname. For these formulas, you must use Cntrl+Shift+Enter. The formula in c8 is

=SUM(IF($C$1:$I$1=$A$8,IF($C$2:$I$5<>"",1)))

<tbody>
</tbody>

The formula for D8 is

=INDEX(A$2:A$5,SMALL(IF($C$1:$I$1=$A$8,IF($C$2:$I$5<>"",ROW($A$2:$A$5)-ROW($A$2)+1)),ROWS($D$8:D8))) First Name Surname 7/1 7/2 7/3 7/4 7/5 7/6 7/7 John Smith A AM PM A Sam Black PM Bill White A A PM James Blue AM Date Quantity Name1 Surname 7/4 2 John Smith Bill White <colgroup><col span="2"><col span="8"></colgroup><tbody> </tbody>

<tbody>
</tbody>