Need your help

[Penny Bangalore]

Hi All,

Below is my data as Table, i just want to look into the table and say, if a particular user is present on particular date i want the result should be "Present" if not leave blank.

(left most data is my table and right data where i need my result , am thinking and thinking of suitable formula for this.)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date	users	counts					Date	user1	user2	user3	user4	user5	user6	user7	user8	user9	user10	user11	user12	user13	user14
2	5/26/2015	user1	201					5/26/2015
3	5/26/2015	user2	221					5/27/2015
4	5/26/2015	user3	240					5/28/2015
5	5/26/2015	user4	238					5/29/2015
6	5/26/2015	user5	217					6/1/2015
7	5/26/2015	user6	233					6/2/2015
8	5/26/2015	user7	222					6/3/2015
9	5/27/2015	user8	245					6/4/2015
10	5/27/2015	user9	229					6/5/2015
11	5/27/2015	user10	202					6/8/2015
12	5/27/2015	user11	227
13	5/27/2015	user12	215
14	5/27/2015	user13	207
15	5/27/2015	user14	200
16	5/27/2015	user15	222
17	5/27/2015	user16	241
18	5/27/2015	user17	246
Sheet1

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	G	H	I	J	K	L	M	N	O	P
1	Date	users	counts			Date	user1	user2	user3	user4	user5	user6	user7	user8
2	5/26/2015	user1	201			5/26/2015	Present	Present	Present	Present	Present	Present	Present
3	5/26/2015	user2	221			5/27/2015								Present
4	5/26/2015	user3	240			5/28/2015
5	5/26/2015	user4	238			5/29/2015
6	5/26/2015	user5	217			6/1/2015
7	5/26/2015	user6	233			6/2/2015
8	5/26/2015	user7	222			6/3/2015
9	5/27/2015	user8	245			6/4/2015
10	5/27/2015	user9	229			6/5/2015
11	5/27/2015	user10	202			6/8/2015
12	5/27/2015	user11	227
13	5/27/2015	user12	215
14	5/27/2015	user13	207
15	5/27/2015	user14	200
16	5/27/2015	user15	222
17	5/27/2015	user16	241
18	5/27/2015	user17	246
Sheet

 

[gaz_chops]

Try this array entered formula (enter with Ctrl, Shift & Enter)

=IF(ISNUMBER(MATCH($H2&I$1,$A$2:$A$18&$B$2:$B$18,0)),"Present","")

or non array

=IF(SUMPRODUCT(($A$2:$A$18=$H2)*($B$2:$B$18=I$1))>=1,"Present","")

 

[SpillerBD]

... or is your Table a Pivot Table?

 

[Penny Bangalore]

ADVERTISEMENT

Hi,

i got the answer .. thanks

 

[Penny Bangalore]

Hi,

working thanks

 

[Penny Bangalore]

ADVERTISEMENT

working .. thanks a lot

 

[Penny Bangalore]

No its not.. its just a table

 

[gaz_chops]

Did they work ok?

 

[Penny Bangalore]

Yes sir