Get result for one column if data associated with that column is equal to two other columns

Question

I have data like this in Excel:

ColA         ColB        ColC           ColD
1B                         1B            E 
2B                         4B            C
3B                         5B            E
4B
5B

I would appreciate a formula to be applied in ColB for the following result:

ColA         ColB        ColC           ColD
1B            E            1B            E 
2B                         4B            C
3B                         5B            E
4B            C
5B            E

possible duplicate of [Comparing two columns, and returning a specific adjacent cell in Excel](http://stackoverflow.com/questions/18191835/comparing-two-columns-and-returning-a-specific-adjacent-cell-in-excel) — Jerry, Feb 12 '14 at 17:49

score 2 · Accepted Answer · answered Feb 12 '14 at 17:58

2

I think this may be much simpler than the possible duplicate suggested - ie just a very standard LOOKUP (which is duplicated elsewhere even MORE often!):

in B2 and copied down to suit:

=IFERROR(VLOOKUP(A2,C:D,2,0),"")

answered Feb 12 '14 at 17:58

pnuts

58,317
11
87
139

Dan85 · Answer 2 · 2014-02-12T18:05:28.313

0

in cell B2, enter

=IFERROR(INDEX($D$2:$D$4,MATCH(A2,$C$2:$C$4,0)),"")

and paste down

Should do the trick!

edited Feb 12 '14 at 18:05

answered Feb 12 '14 at 17:59

Dan85

5
1
4

assuming the row with 1B is the row 2 – Dan85 Feb 12 '14 at 18:06

Get result for one column if data associated with that column is equal to two other columns

2 Answers2