lookup & Indirect

sushantrao

Board Regular
Hi All,
New Microsoft Excel Worksheet.xls
ABCDE
1
2Date
31-Sep
42-SepTime
5Agent Name
6Allan
7David
8
9
Sheet1
New Microsoft Excel Worksheet.xls
GHIJKLMNOPQR
1
2
31-Sep2-Sep
4Time10:0011:0012:00Time10:0011:0012:00
5Agent NameAgent Name
6Allan152535Allan655535
7David203040David754525
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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,

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

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.

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.

LOOKUP & INDIRECT

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

sushant

Here you can see the formulas in your sheet.

Hope this helps.
Book1
BCDEFGHIJKLMNOPQ
2Date
301-sep01-sep02-sep
402-sepTimeTime10:0011:0012:00Time10:0011:0012:00
5Agent Name11:00Agent NameAgent Name
6Allan15Allan152535Allan655535
7David20David203040David754525
Sheet4

Replies
10
Views
553
Replies
12
Views
301
Replies
11
Views
325
Replies
2
Views
305
Replies
2
Views
486

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

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.

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

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