lookup & Indirect

sushantrao

Board Regular
Joined
Jun 24, 2006
Messages
151
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

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

sushantrao

Board Regular
Joined
Jun 24, 2006
Messages
151
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Jun 24, 2006
Messages
151
LOOKUP & INDIRECT

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


sushant
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

Forum statistics

Threads
1,144,699
Messages
5,725,824
Members
422,644
Latest member
BethGS

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top