IF Formula, Excel 2010, Choosing in table only those items that are present on another table/list

[jakup5031]

I have established two tables: the first being records over a long period of time(lets call it "AllTime"), including names; the second being only the names of current employees (lets call it "Current"). I am trying to establish an equation in a column of the "All-Time" table where if an employee from "AllTime[Names]" is listed on the "Current" table, it will tell another column to say "Current." If the employee is not on the "Current" list, that column will say "Former."

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]NAMES
[/TD]
[TD]STATUS
[/TD]
[TD]PERFORMANCE
[/TD]
[/TR]
[TR]
[TD]SMITH[/TD]
[TD](FORMULA)[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]JOHNSON[/TD]
[TD](FORMULA)[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD]PERRY[/TD]
[TD](FORMULA)[/TD]
[TD]525[/TD]
[/TR]
[TR]
[TD]HUGHES[/TD]
[TD](FORMULA)[/TD]
[TD]350[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 250, align: right"]
<tbody>[TR]
[TD]SMITH[/TD]
[/TR]
[TR]
[TD]DAVIS[/TD]
[/TR]
[TR]
[TD]CONNOR[/TD]
[/TR]
[TR]
[TD]LENNOX[/TD]
[/TR]
[TR]
[TD]PERRY[/TD]
[/TR]
</tbody>[/TABLE]









In the tables provided, I want the [Status] column for SMITH and PERRY to say "Current," because they are listed to the right, and I want JOHNSON and HUGHES to say "Former," because they are not on the list.

My formula for the [Status] column so far is:

=IFERROR(IF(AllTime[Name]=Current,"Current","Former"),"")

(IFERROR only because if [Name] is blank, I don't want [Status] to say anything.)

It seems to not read the logical_test because every row comes up as "Former."

The AllTime table will be a continuation table where the record keeping will never end. Therefore I am hoping to establish the formula to remove the "Current" title after I have taking that employee's name off of the Current list.

I am basically trying to tell this formula, "if this name matches any of the 15-20 names off of that list, put this in the box."

Thanks.

 

[Special-K99]

Try this in the Status column

=IF(A2="","",IF(ISNA(MATCH(A2,F$1:F$5,0)),"Former","Current"))
and copy down

where A is the NAMES column
and F1:F5 is your second table

Change the F1:F5 to the worksheet/range of your second table

 

[jakup5031]

That is fantastic. Never used ISNA before, or would have thought of using it. Works perfectly thank you.