If Names match on different tabs, enter data from 1st tab row/column into second tab.

[NGB82KS]

So I have 2 tabs 'Roster-Export' & 'Breakout'
The 'Roster-Export' tab is a complete listing of users, certifications, dates of completion etc..
The 'Breakout' tab is a bunch of formulas to break out certain info from the roster, surname, firstname, Mi etc...

I need to have a formula check for exact name matches on the 'Roster-Export' tab (Column A) against the 'Breakout' tab, name (Column J), and if there is a match, I need to to fill in (Column K) of the 'Breakout' tab with the text from 'Roster-Export' tab (Column I). Is this possible?

Example:
'Roster-Export' tab; Column A5 = "doe, john"; Column I5 = "20200201"
'Breakout' tab; Column J20 = "doe, john";
Because the names match, Column K of the 'Breakout' tab would display as "20200201".

 

[etaf]

=Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))
should do that lookup for you

 

[NGB82KS]

=Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))
should do that lookup for you

You're awesome!

 

[etaf]

you are welcome

 

[NGB82KS]

=Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))
should do that lookup for you

Will this value that it fills in be usable for an additional formula that I can introduce into a Pivot Table later?
Management only wants to see a yes or a no, but No has 3 different answers from the K Column lookup to equal No and yes has 2.

Such as =IF((OR([Status]="#N/A",[Status]="No",[Status]="Certification obtained")),"No","Yes") or something like this

 

[etaf]

dont understand
WILL THIS VALUE
is that the lookup value of "20200201"
not sure how that relates to the if()
But any value brought over would still be used in a pivottable

 

[NGB82KS]

dont understand
WILL THIS VALUE
is that the lookup value of "20200201"
not sure how that relates to the if()
But any value brought over would still be used in a pivottable

Ok sorry let me try again..
The formula you gave me "=Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))" returns one of the following as a result:
#N/A (if there is no data in that field)
or a date
or a TBD

Im trying to convert the #N/A and TBD to a "No" in a separate field so when I build a Pivot table it will sum the total yes and the total no

 

[etaf]

yes you can do that - you can also us the IF() on the lookup

=IF( OR( ISERROR(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))), Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))="TBD") , "NO", "YES")

 

[NGB82KS]

yes you can do that - you can also us the IF() on the lookup

=IF( OR( ISERROR(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))), Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))="TBD") , "NO", "YES")

1st of all, you're some kinda excel daemon or wizard not sure which
2nd, it changed the TBD answer to a No, but it still shows #N/A in the cells, so the cells are now Yes, No, #/NA. For some reason I cant make the N/A show up as a No.

 

[etaf]

=IF( ISERROR(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))), "NO", IF(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))="TBD", "NO", "YES"))
Not the best way , as an OR() testing the #N/A still returns #N/A - must be a better way I will Think
OR
=IF( ISNA(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))), "NO", IF(Index('Roster-Export'!I:I,Match('Breakout'!J20,'Roster-Export'!A:A,0))="TBD", "NO", "YES"))