Excel – if value exists in a range then

microsoft excel

I need a formula that will:
Refer to a specific cell E1, check if E1 has any value at B column in table "Table1".
In table "Table1", at B column, there are different values (including duplicated values) corresponding to cell E1. If a specific text "Mary" is available, then return "Mary"; otherwise, return the first data corresponding to that cell.

Example:

enter image description here

Expected results:

F1: Mary
F2: John

Best Answer

Put

=IF(ISERROR(MATCH(E1 & "Mary", A$1:A$8 & B$1:B$8, 0)), VLOOKUP(E1, A$1:B$8, 2, 0), "Mary")

into cell F1 (adjusting the 8 to the number of rows of data that you have)
and press Ctrl+Shift+Enter.  Drag/fill down to Fn if desired.

The MATCH looks for a row where An & Bn (An concatenated with Bn) is E1 & "Mary" (E1 concatenated with "Mary").  Such a row probably has An = E1 and Bn = "Mary".  If that returns an error, return VLOOKUP(E1, A$1:B$8, 2, 0) to F1.  If it succeeds, we found a row with "Mary", so return "Mary".

The Ctrl+Shift+Enter makes the formula an array formula.