vlookup in two paired columns

[bulletproof]

hi, me again.

please help...

  Column A | Column B | Column C | Column D | Column E
1 1a2b3c   | smith, jon| 2d3f4g    | cruz, tom |
2 2w3e4r   | tan, jim  | 5t6y7u   | wong, kim  |
3 2d3f4g   | cruz, tom | 7j8k9l    | lim, suzy |
4 4c5v6b   | dylan, bob| 1a2b3c   | smith, jon |

sorry for the presentation, can't download the tool to post an
image since im in the ofc and no priviledge to install add ins.

Im trying to see which ones from column C + D are in
columns A + B.

column A is the ID of the person name in column B.
column C is the ID of the person name in cloumn D.
so on, so forth...

im trying to figure out a lookup formula to find the values in
column C + D in column A + B, then show the result in column E.

the result should be the ID + name.

I'm not too literate in excel...
not quite sure if this is even possible.

thanks!

 

[Aladin Akyurek]

Try...

E1, control+shift+enter, not just enter, and copy down:

=INDEX($A$1:$A$4&" | "&$B$1:$B$4,MATCH(1,IF($A$1:$A$4=C1,IF($B41:$B$4=D1,1)),0))

 

[bulletproof]

sir,


does the " | " icluded in the string?

 

[bulletproof]

nothing happens, sir.

not quite sure what that string is referring to.

anyone?

 

[Aladin Akyurek]

nothing happens, sir.

not quite sure what that string is referring to.

anyone?

Did you apply control+shift+enter?

 

[bulletproof]

sorry sir, here's what i did:

i went to cell E1, then ctrl+shift+enter..
how do I put in
=INDEX($A$1:$A$4&" | "&$B$1:$B$4,MATCH(1,IF($A$1:$A$4=C1,IF($B41:$B$4=D1,1)),0))

i feel so dumb...

 

[Aladin Akyurek]

sorry sir, here's what i did:

i went to cell E1, then ctrl+shift+enter..
how do I put in
=INDEX($A$1:$A$4&" | "&$B$1:$B$4,MATCH(1,IF($A$1:$A$4=C1,IF($B41:$B$4=D1,1)),0))

i feel so dumb...

Type in E1 then hit the control+shift+enter keys at the same time:

=INDEX($A$1:$A$4&" | "&$B$1:$B$4,MATCH(1,IF($A$1:$A$4=C1,IF($B$1:$B$4=D1,1)),0))

 

[bulletproof]

finally got it sir, sorry for being a dumb-a$$ :D
thank you.

can i drag those formula or how do I copy it
to function as the same to the entire list?

 

[Aladin Akyurek]

finally got it sir, sorry for being a dumb-a$$ :D
thank you.

can i drag those formula or how do I copy it
to function as the same to the entire list?

Just drag it down.

 

[bulletproof]

Just drag it down.

did it. but only E1 has the correct entry.
all are #N/A...

and I do know how to copy-drag a formula sir...

any way i can post an image here?