Lookup of MAC addresses

[Eyez01]

Hi all,

I have a spread sheet of MAC addresses and I'm trying to use a VLookup to search for a MAC address value and return values from hostname, OS and whether the device is virtual or not. At first I kept getting a N/A so then turned to trying to use INDEX & MATCH but still can't get it to work. Does anyone have any suggestions?

MAC Address 1	MAC Address 2	MAC Address 3	MAC Address 4	MAC Address 5	Host Name	OS	Virtual
00:11:22:33:44:55	00:11:22:33:44:5a	00:11:22:33:44:5b	00:11:22:33:44:5d	00:11:22:33:44:5c	PDC001	Windows	Yes
	ab:11:22:33:44:55	ff:11:22:33:44:5a	rr:11:22:33:44:5b	ht:11:22:33:44:5d	APPINT001	AIX	No

 

[earthworm]

Book1
	B	C	D	E	F	G	H	I	J	K
2	Inset Here	Host Name	OS	Virtual
3	00:11:22:33:44:5c	PDC001	Windows	Yes			MAC Address	Host Name	OS	Virtual
4							00:11:22:33:44:55	PDC001	Windows	Yes
5							00:11:22:33:44:5a	PDC001	Windows	Yes
6							00:11:22:33:44:5b	PDC001	Windows	Yes
7							00:11:22:33:44:5d	PDC001	Windows	Yes
8							00:11:22:33:44:5c	PDC001	Windows	Yes
9							ab:11:22:33:44:55	APPINT001	AIX	No
10							ff:11:22:33:44:5a	APPINT001	AIX	No
11							rr:11:22:33:44:5b	APPINT001	AIX	No
12							ht:11:22:33:44:5d	APPINT001	AIX	No
Sheet1

Cell Formulas
Range	Formula
C3	C3	=VLOOKUP($B3,$H:$K,2,0)
D3	D3	=VLOOKUP($B3,$H:$K,3,0)
E3	E3	=VLOOKUP($B3,$H:$K,4,0)

 

[Peter_SSs]

Welcome to the MrExcel board!

Keeping your existing layout, and assuming addresses do not appear more than once in the table, try ..

21 09 03.xlsm
	A	B	C	D	E	F	G	H
1	MAC Address 1	MAC Address 2	MAC Address 3	MAC Address 4	MAC Address 5	Host Name	OS	Virtual
2	00:11:22:33:44:55	00:11:22:33:44:5a	00:11:22:33:44:5b	00:11:22:33:44:5d	00:11:22:33:44:5c	PDC001	Windows	Yes
3		ab:11:22:33:44:55	ff:11:22:33:44:5a	rr:11:22:33:44:5b	ht:11:22:33:44:5d	APPINT001	AIX	No
4
5
6
7	Mac Address	Host Name	OS	Virtual
8	00:11:22:33:44:5b	PDC001	Windows	Yes
9	00:11:22:33:44:55	PDC001	Windows	Yes
10	ht:11:22:33:44:5d	APPINT001	AIX	No
Mac Address Details

Cell Formulas
Range	Formula
B8:D10	B8	=INDEX(F:F,SUMPRODUCT(ROW($A$2:$E$3)*($A$2:$E$3=$A8)))

 

[Eyez01]

Hi @earthworm. Thank you for the response. I wanted to avoid having to format the data as the data set I'm working with is quite large.

Hi @Peter_SSs that is brilliant. Could you explain a little about the formula that you have used to do this. Rather than just using it I want to understand a bit more so I can take the learn away Thanks

 

[Peter_SSs]

Glad it worked for you.

Could you explain a little about the formula that you have used to do this.

Hmm, not that easy to explain, but let's try. Take the formula in B8 in my mini-sheet
=INDEX(F:F,SUMPRODUCT(ROW($A$2:$E$3)*($A$2:$E$3=$A8)))
So, we are going to retrieve a value from column F: =INDEX(F:F, ...

SUMPRODUCT(ROW($A$2:$E$3)*($A$2:$E$3=$A8))

ROW($A$2:$E$3) = {2,3}

The Mac address in A8 is the same as the one in C2 so ($A$2:$E$3=$A8) evaluates to the array {FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}

So SUMPRODUCT({2,3}*{FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}) produces
SUM{0,0,2,0,0;0,0,0,0,0}
which sums to 2

Therefore we get INDEX(F:F,2) = PDC001

 

[Eyez01]

I've been testing this a little more and have a found that if the MAC address cannot be matched, the formula returns values of servers at the start of the list. Is there a way to say if the MAC address is not found, return a value, e.g. N/A?

 

[Peter_SSs]

assuming addresses do not appear more than once in the table,

Still with the above assumption, try

21 09 03.xlsm
	A	B	C	D	E	F	G	H
1	MAC Address 1	MAC Address 2	MAC Address 3	MAC Address 4	MAC Address 5	Host Name	OS	Virtual
2	00:11:22:33:44:55	00:11:22:33:44:5a	00:11:22:33:44:5b	00:11:22:33:44:5d	00:11:22:33:44:5c	PDC001	Windows	Yes
3		ab:11:22:33:44:55	ff:11:22:33:44:5a	rr:11:22:33:44:5b	ht:11:22:33:44:5d	APPINT001	AIX	No
4
5
6
7	Mac Address	Host Name	OS	Virtual
8	00:11:22:33:44:5b	PDC001	Windows	Yes
9	00:11:22:33:44:55	PDC001	Windows	Yes
10	ht:11:22:33:44:5d	APPINT001	AIX	No
11	abc-def-ghi	N/A	N/A	N/A
12	00:11:22:33:44:5c	PDC001	Windows	Yes
Mac Address Details

Cell Formulas
Range	Formula
B8:D12	B8	=IF(COUNTIF($A$2:$E$3,$A8),INDEX(F:F,SUMPRODUCT(ROW($A$2:$E$3)*($A$2:$E$3=$A8))),"N/A")