Lookup based on certain condition

[jw01]

Hi all

Wondering if it's possible for vlookup or index/match feature to include logic so it selects an answer based on "last active cell".

For example, I have a list of employees and the "# of calls" they make. One individual is listed 3 times b/c they changed groups. The data shows the # of calls by months from July 2015-May 2018. When I do a vlookup or index/match, it returns the first value it finds....value I want to find is what "group" they work for, which is listed in column "B" in the productivity tab

Is there any way to add logic in there where it scams through columns "H:AS" in the "productivity" tab and sees, for i.e. "Doe, John", which row of the 3 groups, has the most active data. For example, "Doe, John, is listed in productivity tab, in row 11:13. Row 13 shows the most active data, as row 11 and row 13 show 0 for month of may.

I want my vlookup or index/match in the Lookup to show "intellectual property & tech" as the answer ; my current formula shows "employement labor".

can someone pls help?! I have attached a sample sheet.

[Special-K]

Add a column with a unique number, in your case 1, 2, 3, 4.

Use that number to perform the INDEX.
This will cater for any future events where an employee move from PGG to another PGG and back to their original PGG.

[jason.b75]

To clarify, should it be the most recent activity, or the most activity, as this line of your post implies?

Is there any way to add logic in there where it scams through columns "H:AS" in the "productivity" tab and sees, for i.e. "Doe, John", which row of the 3 groups, has the most active data. For example, "Doe, John, is listed in productivity tab, in row 11:13. Row 13 shows the most active data, as row 11 and row 13 show 0 for month of may.

edit:-

Assuming most recent,

Enter into Productivity B11 and fill down

=IFERROR(MATCH(1E+100,H11:AS11),)

Then use this array lookup formula

=INDEX(Productivity!$B$11:$B$14,MATCH(TRUE,IF(Productivity!$E$11:$E$14=D13,Productivity!$BA$11:$BA$14=MAX(IF(Productivity!$E$11:$E$14=D13,Productivity!$BA$11:$BA$14))),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Does that help?

[jw01]

Hi Jason.b75

The formula you mention to enter in "productivity" cell B11 down - I CANT do that as that's the input from the system.

I placed this formula
=IFERROR(MATCH(1E+100,H11:AS11),)

I assume the above formula should go in column "AZ" in productivity and the array formula then becomes:
{=INDEX(Productivity!$B$11:$B$14,MATCH(TRUE,IF(Productivity!$E$11:$E$14=D12,Productivity!$AZ$11:$AZ$14=MAX(IF(Productivity!$E$11:$E$14=D12,Productivity!$AZ$11:$AZ$14))),0))}

is that correct? thxs

[jason.b75]

Sorry, that was a typo, I should have said BA11, not B11.

Using the formula in AZ 11 will work exactly the same, and your edit to the formula is correct.

[jw01]

that worked amazing Jason.b75 thx you soo soo much!!!