Excel formula - vlookup/if/index?

[grtct]

Hello,

I need a formula that will:
Refer to a specific cell E1, check if E1 has any value at B column in table "Table1" (A1:B8). 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:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]a123
[/TD]
[TD]Paul
[/TD]
[TD][/TD]
[TD][/TD]
[TD]a123
[/TD]
[TD]Mary
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]a123
[/TD]
[TD]John
[/TD]
[TD][/TD]
[TD][/TD]
[TD]b123
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]a123[/TD]
[TD]Mary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]a123
[/TD]
[TD]Harry
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]a123
[/TD]
[TD]Peter
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]b123
[/TD]
[TD]John
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]b123[/TD]
[TD]Peter
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]b123
[/TD]
[TD]Harry
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Expected results:
F1: Mary
F2: John



Thank you!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Code​

[/TD]
[TD]

Names​

[/TD]
[TD]

Criteria​

[/TD]
[TD][/TD]
[TD]

Code​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

a123​

[/TD]
[TD]

Paul​

[/TD]
[TD]

Mary​

[/TD]
[TD][/TD]
[TD]

a123​

[/TD]
[TD]

Mary​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

a123​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

b123​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

a123​

[/TD]
[TD]

Mary​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

a123​

[/TD]
[TD]

Harry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

a123​

[/TD]
[TD]

Peter​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

b123​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

b123​

[/TD]
[TD]

Peter​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

b123​

[/TD]
[TD]

Harry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in F2 copied down
=IF(COUNTIFS(A:A,E2,B:B,C$2),C$2,INDEX(B:B,MATCH(E2,A:A,0)))

Hope this helps

M.

 

[grtct]

Welcome to Mr Excel forum

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Code​

[/TD]
[TD]

Names​

[/TD]
[TD]

Criteria​

[/TD]
[TD][/TD]
[TD]

Code​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

a123​

[/TD]
[TD]

Paul​

[/TD]
[TD]

Mary​

[/TD]
[TD][/TD]
[TD]

a123​

[/TD]
[TD]

Mary​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

a123​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

b123​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

a123​

[/TD]
[TD]

Mary​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

a123​

[/TD]
[TD]

Harry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

a123​

[/TD]
[TD]

Peter​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

b123​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

b123​

[/TD]
[TD]

Peter​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

b123​

[/TD]
[TD]

Harry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in F2 copied down
=IF(COUNTIFS(A:A,E2,B:B,C$2),C$2,INDEX(B:B,MATCH(E2,A:A,0)))

Hope this helps

M.

Excellent
This helps me a lot. : )
Thank you.