Two way lookup with dynamic shift of start point

I need help on vlookup / index etc.
I have two tables :
First is a JIRA output : - I only post pertinent data here :
Issue key Assignee
REG-497 dmuthaiya
REG-495 gpai
REG-494 vkumbheshwar
REG-493 hgupta
REG-492 vkumbheshwar
REG-491 krajasekar
REG-490 krajasekar
REG-489 gpai
REG-488 hgupta

Other is a task assignment table ..
This is by Asignee - task number and date when to be executed.

KRAJSEKAR VKUMBHESHWAR DMUTHIAYA HGUPTA GPAI Date
REG-374 REG-479 REG-74 REG-408 4-Jun
REG-375 REG-479 REG-74 4-Jun
BUILD REG-458 REG-475 REG-477 REG-410 6-Jun
REG-374 REG-402 REG-475 REG-477 REG-411 6-Jun
REG-374 REG-402 REG-476 REG-72 REG-409 7-Jun
REG-376 REG-402 REG-476 REG-72 REG-409 7-Jun
REG-376 REG-402 REG-476 REG-72 REG-409 10-Jun
REG-376 REG-402 REG-476 REG-72 REG-409 10-Jun
REG-376 REG-402 REG-476 Reg-484 REG-483 11-Jun
Reg-378 REG-402 REG-476 Reg-484 REG-483 11-Jun
REG-377 REG-402 REG-476 Reg-485 REG-79 12-Jun
REG-379 REG-402 REG-476 REG-486 REG-364 12-Jun


What I need is in the first table - I want to put against each issue when it is expected to be complete (that's 'Date' column in second table).

Now I can use reverse lookup formula IF i know which column to look into (that's asignee in table - 1)
something like :

=LOOKUP(1, 1/(D2:D38=N3), J2:J38)

But what I am struggling is based on 'Asignee' how do I select different column (in place of D) for this formula.

Another way I can manage this is simple vlookup - provided I am able to define a different 'startpoint' for first column of lookup table in vlookup - same issue there too.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

It's hard to follow data when you copy and paste to the browser, the formatting is lost so we can't see how the columns line up.

Best guess without the sample workbook that Pepe has asked for.

Formula:

Please Login or Register  to view this content.

$J$2:$J$13 = column of dates in table 2
N3 = Issue key (REG-xxx) from table 1 to look for in table 2.
O3 = Name from table 1 to look for in table 2.
$D$2:$I$13 = Range of issue keys in table 2.
$D$1:$I$1 = Row of names in table 2.

Note that in your sample, you have a spelling difference in at least one the names, dmuthaiya in table 1, but DMUTHIAYA in table 2.

ENclosing file TwoWayLookup.xlsx ...
The Worksheet Tasklist is the mastersheet .. Here data needs t be 'looked up' from sheet 'Schedule'.

Try this in D2:

=INDEX(Schedule!C:C,AGGREGATE(14,6,ROW(Schedule!D$3:H$107)/(Schedule!D$3:H$107=A2),1))

How about

Formula:

Please Login or Register  to view this content.

When there are multiple matches, which is the 'correct' date that the formula should return?

Looking at row 18 in the tasklist as an example, REG-402 vkumbheshwar.

In the Schedule, this combination appears with the dates, 06-Jun, 07-Jun (Twice), 10-Jun (Twice), 11-Jun (Twice), 12-Jun (Twice) and 13-Jun. A total of 10 entires, so which of them is the correct one?

Originally Posted by jason.b75

When there are multiple matches, which is the 'correct' date that the formula should return?

I believe the OP is looking for the latest date as seen in cell E2 of their sample workbook.

I think it's also the latest date, but based on the Asignee.
For instance row 14 & 15 are both REG-384 but with different names in col B

Good point Fluff. I didn't even notice the Assignee names.

This takes them into account (as does the formula in post #6):
=INDEX(Schedule!C:C,AGGREGATE(14,6,ROW(Schedule!D$3:H$107)/((Schedule!D$3:H$107=A2)*(Schedule!D$2:H$2=B2)),1))

You both make a valid point, but given how often we see inaccurate samples, I was paying more attention to the repeated entries that the 2 expected results provided.
Based on the requirement being the latest date in the list.

Formula:

Please Login or Register  to view this content.

The dates don't need to be indexed when using aggregate.

Or with the latest version of excel

Formula:

Please Login or Register  to view this content.

Originally Posted by jason.b75

The dates don't need to be indexed when using aggregate

Good point.

My sincere apologies - for the confusion.
The problem is to find when a task is ending .. So the task (e.g. REG-327) will have asignee and it is also listed under that asignee .. I want to find the last entry. Basically - I am trying to get a sense of end date for each of the tasks so that I can later create a task assignment for day.

Have you tried any of the solutions given?

As Fluff has pointed out above, you already have several suggestions which will work as you have asked.

Based on what you have said in post #13, I'm going to assume that the reason for each date appearing twice in the schedule, is to split the day in half. In which case, as an alternative to the existing suggestions (all of which will return the correct dates), I'm going to add this one to identify which part of the day as well.

Formula:

Please Login or Register  to view this content.

Note that for this one to work, you will need to change your custom format for column D of the tasklist to dd-mmm AM/PM

You might want to look at dynamic named ranges or a structured table for the schedule sheet so that you don't have to change the formulas or make them inefficient by adding more rows than you need.

One last point, you don't need the VLOOKUP formula in column B of the schedule, in B2, simply enter =C2 then format it as ddd and fill down.

Originally Posted by jason.b75

As Fluff has pointed out above, you already have several suggestions which will work as you have asked.

Based on what you have said in post #13, I'm going to assume that the reason for each date appearing twice in the schedule, is to split the day in half. In which case, as an alternative to the existing suggestions (all of which will return the correct dates), I'm going to add this one to identify which part of the day as well.

Formula:

Please Login or Register  to view this content.

Note that for this one to work, you will need to change your custom format for column D of the tasklist to dd-mmm AM/PM

You might want to look at dynamic named ranges or a structured table for the schedule sheet so that you don't have to change the formulas or make them inefficient by adding more rows than you need.

One last point, you don't need the VLOOKUP formula in column B of the schedule, in B2, simply enter =C2 then format it as ddd and fill down.

Yes - I tried out the solution and it works as expected. Thanks. I was thinking about aggregate function; but was unable to formulate solution.