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:

Expected results:

F1: Mary

F2: John

## Best Answer

Put

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`F`

if desired.nThe

`MATCH`

looks for a row where`A`

(n& Bn`A`

concatenated withn`B`

) isn`E1 & "Mary"`

(`E1`

concatenated with`"Mary"`

). Such a row probably has`A`

=n`E1`

and`B`

=n`"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+Entermakes the formula an array formula.