Solved vlookup for duplicate ip address to hostname lookup

February 26, 2019 at 09:04:47
Specs: Windows 10, Intel core i5 3570K
Say I have in column A1 - A59 = IP addresses
C1 - C59 = hostnames for those IP addresses

Now in A60 I have an IP address , same ip's as A1 - A59 but now they contain duplicate entries
so the same ip can show up in A60 - A90

Here is the kicker, I dont have hostnames associated with A60 - A90 Ip address in C60 - C90

but If I look in A1 - A59 i can see the associated hostname, bad thing is , I have like 20,000 lines and want to automate this

Here is what I did, created another column so A column has ip address , B column blank(for my vlookup), c column has hostnames upto C60

I want those hostname from c 1 - c60 to associate for my ip addresses in A60 - A90

So I did this
=VLOOKUP(A:A,A1:C60,3,FALSE)

This works for the one cell i made the vlookup function initially for. but as I hover over the square for the black cross , and drag it starts incrementing it I.E.
=VLOOKUP(A:A,A1:C60,3,FALSE)
=VLOOKUP(A:A,A2:C61,3,FALSE)

That causes it to give me the next hostname associated with the ip, but thats not the correct hostname for that ip , thats the hostname for the next ip but i still have 30 duplicate ip's that i want the same hostname as (A:A,A1:C60,3,FALSE) associated with

I know this is confusing wording, i really dont know how else best to explain


See More: vlookup for duplicate ip address to hostname lookup

Report •

#1
February 26, 2019 at 09:36:55
✔ Best Answer
Did you try this?

=VLOOKUP(A:A,$A$1:$C$60,3,FALSE)

The dollar signs prevent the formula from changing when you drag it.

For an overview of relative, absolute, and mixed references, check out this link:

https://support.office.com/en-us/ar...

message edited by DerbyDad03


Report •

#2
February 26, 2019 at 09:51:28
You are a genius! thank you so much!

Report •
Related Solutions


Ask Question