Look up values across two columns and display value from third column

lolly150

Board Regular
Joined
Jan 9, 2010
Messages
91
Please can someone help with my dilemma:

I need to notify line managers which of their team are out of date with training requirements

Colum A is Line Manager
Column B is staff member
Columns C-E are for training results, Capacity,Fire and Equality. The only entry in these columns would be "non compliant", although some cells may be blank.

I'd like a formula to look up a specific line manager in column A, e.g: Mandy Jones, then look up "non compliant" in column C for example and then show the result as the corresponding staff member.

Is any one able to help with this?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I'm assuming the following, you'll need to adjust the formula to match your data

in Sheet1 A1 specific line manager

in A3
=IFERROR(INDEX(Sheet2!$B$1:$B$1000,SMALL(IF((Sheet2!$A$1:$A$1000=A1)*((C$1:C$1000="non compliant")+(D$1:D$1000="non compliant")+(E$1:E$1000="non compliant")),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter

or if you can use AGGREGATE

=IFERROR(INDEX(Sheet2!B$1:B$1000,AGGREGATE(15,6,ROW(A$1:A$1000)/(Sheet2!A$1:A$1000=A1)*((C$1:C$1000="non compliant")+(D$1:D$1000="non compliant")+(E$1:E$1000="non compliant")),ROWS(A$1:A1000))-(1-1),1),"")

and copy whichever formula you use down as far as however many rows of data there are in Sheet2
 
Last edited:
Upvote 0
Thanks for your reply.
I'm a little confused about where I need to enter the formula. My data is on a sheet named 'Results' and is about 200 rows. The training results data (i.e. non compliant) is in columns C-U
 
Upvote 0
That information wasn't in your original description.
Also in your original description you said "non compliant" was in columns C-E not C-U.

I'll have to rethink the formula, not sure this is gonna work now...
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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
Back
Top