lookup & Indirect

[sushantrao]

Hi All,

New Microsoft Excel Worksheet.xls
	A	B	C	D	E
1
2		Date
3		1-Sep
4		2-Sep		Time
5		Agent Name
6		Allan
7		David
8
9
Sheet1

New Microsoft Excel Worksheet.xls
	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3				1-Sep					2-Sep
4			Time	10:00	11:00	12:00		Time	10:00	11:00	12:00
5		Agent Name					Agent Name
6		Allan		15	25	35	Allan		65	55	35
7		David		20	30	40	David		75	45	25
8
9
10
11
12
Sheet1

I want the respective data in table 1 -D6 & D7 (yellow one)from
raw data which is in second table
Reqiuere formula

Thanks

 

[fairwinds]

Hi,

Please tell us what result you expect and on what criterias it is based.

 

[sushantrao]

lookup & indirect...urgent hel needed.

Criteria:

1. Date
2. Agent Name
3. Time

so i can get a on a particular date a particular agent & on a particular time the status of agent which will be in table 2.

 

[fairwinds]

Assuming you enter time in D5, try:

=INDEX($I$6:$Q$7,MATCH(B6,$H$6:$H$7,0),MATCH($D$5,OFFSET($I$3,1,MATCH($B$3,$J$3:$Q$3,0),1,5),0)+MATCH($B$3,$J$3:$Q$3,0)-1)

in D6 and drag down.

I would however suggest to reconsider the layout in order to avoid such an ugly formula.

 

[sushantrao]

LOOKUP & INDIRECT

Formula give a N/A result for all the fields. is it working or not
require help


sushant

 

[fairwinds]

Here you can see the formulas in your sheet.

Hope this helps.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Date
3	01-sep								01-sep					02-sep
4	02-sep		Time					Time	10:00	11:00	12:00		Time	10:00	11:00	12:00
5	Agent Name		11:00				Agent Name					Agent Name
6	Allan		15				Allan		15	25	35	Allan		65	55	35
7	David		20				David		20	30	40	David		75	45	25
Sheet4