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."

NAMES	STATUS	PERFORMANCE
SMITH	(FORMULA)	500
JOHNSON	(FORMULA)	550
PERRY	(FORMULA)	525
HUGHES	(FORMULA)	350

<tbody>
</tbody>

SMITH

DAVIS

CONNOR

LENNOX

PERRY

<tbody>
</tbody>

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.