Excel need to match and paste to an adjacent cell on sheet 1

Question

So, on sheet 2, I have a set of IP addresses in column A, that are matched to coordinates in column B. On sheet 1, I have all of those IP addresses scattered around. I need a formula on sheet 1, that looks at sheet 2 and if it finds the IP address match from column A, it will paste the corresponding coordinates in column B, to the adjacent cell on sheet 1.

** * Still a little confused guys, thank you for the answers, I have made a better image, so that the actual columns can be used when answering. Image So the formula needs to look at column D, go over to sheet 2, if it finds a match in column A, then paste the coordinates in column B into sheet 1 column E.

Answer 1

=vlookup(Cell on sheet1 with IP address in it, sheet 2 all of column A and B with your date,2,0)

Lock you reference on sheet2 with $ in front of the letters and number and then you should be able to copy and paste this formula just making sure that the first argument is referencing the right cell you want. I would also wrap it in an error formula like IFERROR to deal with situations where the IP address is not found.

so to make it a little clearer. Assume your ip addresses are on sheet2 A1:A7 and coordinates are on sheet2 B1:B7, and the IP address you want to search for is in C3 then I would use the following formula in cell C4

=vlookup(c3,sheet2!$A$1:$B$7,2,0)

or if you want error checking in it you might go with

=iferror(vlookup(c3,sheet2!$A$1:$B$7,2,0),"NOT FOUND")

NOTE: Not all version of excel support the IFERROR formula

Answer 2

You can use INDEX/ MATCH to solve this problem.

=INDEX(Sheet2!$C$3:$C$12,MATCH(Sheet1!B3,Sheet2!$B$3:$B$12,0))

I've used made up IP address and coordinates, but the principles are still the same. Here is sheet 2 where they are all matched:

Here is sheet1, where you have the IP addresses to lookup. I've scrambled them up so you can see that they are being correctly mapped to their corresponding coordinates:

In C3, I've entered the INDEX MATCH formula, and I've tried to break down the formula and what's it doing in this scenario.

The last thing to do is to autofill the rest of the coordinates with the formula:

They have all been successfully mapped.

NOTE: IF the formula does not find a match, it will read #N/A, which is not pretty to look at. To fix this, you can add an IFERROR statement to the formula:

=IFERROR(INDEX(Sheet2!$C$3:$C$12,MATCH(Sheet1!B3,Sheet2!$B$3:$B$12,0)),"no match")

That will basically just generate a "no match" message in any cell that is not matched.