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

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.

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 •

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


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:

message edited by DerbyDad03

Report •

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

Report •
Related Solutions

Ask Question