Match/Index/Indirect Formula??

[pujo]

Good day all!
It's been a while and need some formula assistance.

Column A I have a large list of MAC addresses I pulled from a switch.
Column B I have another list of MAC addresses pulled from a different switch.
Column C is the list of IP addresses associated with the MAC addresses in column B.

I'm trying to figure out the formula to find the matched MAC addresses from column A and Column B outputting the matched MAC address in column D and the associated IP address in Column C that has a match in column D.

Thanks!
PuJo

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[pujo]

Updated!

On this windows machine, I am using Office '13.

 

[Fluff]

Thanks for that.
Try in D2 dragged down

=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/(ISNUMBER(MATCH($A$2:$A$20,$B$2:$B$20,0))),ROWS(D$2:D2))),"")

and in E2 dragged down

=IF(D2="","",INDEX($C$2:$C$20,MATCH(D2,$B$2:$B$20,0)))

 

[pujo]

Thanks @Fluff !

 

[Fluff]

You're welcome & thanks for the feedback.