Formula to lookup data based on 2 criteria

Hi Guys

Hope some one can help,

I need a formula to lookup some data after matching two columns


b8:b63 contain drivers names, i need to look for the name in column B8:63, match it with the name in column AM9:64,
Then match the date in B2 with date in cells AN8:EQ8 and return the corresponding value to Cell E8:E63

For instance if the driver is called driver1 and the date is the 01/12/12 column E would be 10.75, for driver2 8.75, for driver3 8.75

B..........E..........AM.............AN...............AO.................AP
......................................01/12/12.......08/12/12.......15/12/12
Driver1,.10.75......Driver1.........10.75...........8.75..............8.75
Driver2,..8.75.......Driver2..........8.75...........10.75..............8.75
Driver2,..8.75.......Driver3..........8.75............8.75..............10.75

and when the date changes to the 08/12/12 in B2 the value in E changes for each driver,



Thanks

Rich

Welcome to the forum, can you edit the title of your thread that would better explain your query.
as per forum rules.
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

hi rthomas, i'm a little confused about this part:

b8:b63 contain drivers names, i need to look for the name in column B8:63, match it with the name in column AM9:64

they dont start & end at the same row?
try:

=INDEX($AN$9:$AP$64,MATCH(A8,$AM$9:$AM$64,0),MATCH($B$2,$AN$8:$EQ$8,0))

if there will be instances no match are found & you want to show a blank, then use ISNA:

=IF(ISNA(INDEX($AN$9:$AP$64,MATCH(A8,$AM$9:$AM$64,0),MATCH($B$2,$AN$8:$EQ$8,0))),"",INDEX($AN$9:$AP$64,MATCH(A8,$AM$9:$AM$64,0),MATCH($B$2,$AN$8:$EQ$8,0)))

if it doesnt work, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.