Another Excel question

Good morning MyBB

I have 2 spreadsheets.....

File 1 = list with about 40000 rows, with coordinates
File 2 = file 1 was filtered by someone, to bring it down to about 400 rows, but the coordinates column was removed, along with alot of information that is not needed. I need the coordinates to plot the info on a GIS system

both files have a column that contains a a unique code that i can use.

how can i merge the 2 files, to get the coords on file 2 ??

thanks
Are you sure that the collumn is deleted, and just not hidden?
 
The sheet with the values you are looking up, that column A needs to have unique values otherwise vlookup will return the first match or nearest match depending on your parameters.

To see if your column is unique try

And vlookup is ancient.

use xlookup

@Cray

i am lost

i put the files in 1 new spreadsheet with 2 sheets

Sheet 1 contains the coordinates that I need in Columns F and G

1714721560650.png

i want the data in F and G to be in sheet 2, under J and K (D is the unique identifier)

1714721783608.png
 
use a vlookup ... = vlookup in column x

vlookup($d2, 'sheet1'!$D:$G,3,false)

for coilumn Y same formula but change to 4 but to get the G value
 
@Cray

i am lost

i put the files in 1 new spreadsheet with 2 sheets

Sheet 1 contains the coordinates that I need in Columns F and G

View attachment 1702817

i want the data in F and G to be in sheet 2, under J and K (D is the unique identifier)

View attachment 1702819
I would help further but I need to know. Is this used for land grabbing?

And vlookup will work here. Just watch a video or read the Microsoft help file. It takes 5 minutes to learn.
 
Inside J2 insert "=vlookup(D2, sheet1!D:G,3,0)"
Inside K2 insert "=vlookup(D2, sheet1!D:G,4,0)"
 
@Cray

i am lost

i put the files in 1 new spreadsheet with 2 sheets

Sheet 1 contains the coordinates that I need in Columns F and G

View attachment 1702817

i want the data in F and G to be in sheet 2, under J and K (D is the unique identifier)

View attachment 1702819

Okay, if you want to return the co-ordinates to two different cells you will need a slightly different formula for each cell (the difference being the value returned based on the matched column)

So in sheet two, J2

=VLOOKUP(D2,Sheet1!D:G,3,0)

Sheet two, Column K2

=VLOOKUP(D2,Sheet1!D:G,4,0)

The change, 3 versus 4 is telling excel to return the 3rd or 4th value in the lookup range. (D:G)

Updated - thanks @The_MAC
 
Last edited:
I would help further but I need to know. Is this used for land grabbing?

And vlookup will work here. Just watch a video or read the Microsoft help file. It takes 5 minutes to learn.
no grabbing....

:)
 
Okay, if you want to return the co-ordinates to two different cells you will need a slightly different formula for each cell (the difference being the value returned based on the matched column)

So in sheet two, J2

=VLOOKUP(D1,Sheet1!D:G,3,0)

Sheet two, Column K2

=VLOOKUP(D1,Sheet1!D:G,4,0)

The change, 3 versus 4 is telling excel to return the 3rd or 4th value in the lookup range. (D:G)
Make sure you understand the formula. If its going to be your spreadsheet anyway.
 
Okay, if you want to return the co-ordinates to two different cells you will need a slightly different formula for each cell (the difference being the value returned based on the matched column)

So in sheet two, J2

=VLOOKUP(D1,Sheet1!D:G,3,0)

Sheet two, Column K2

=VLOOKUP(D1,Sheet1!D:G,4,0)

The change, 3 versus 4 is telling excel to return the 3rd or 4th value in the lookup range. (D:G)
actually, that will fail (logically), needs to be D2
 
As others have mentioned lookup should work just fine, or my preference xlookup the "never" version.

Just be mindful if you have duplicates in your initial "search for" value, the results won't be accurate.

I would probably check if there are duplicates 1st, then run the lookup.

If you have duplicates, report back as there are ways around this
 
I have been told this before, and will get to it one of these days, Vlookup works for me. :p
xlookup you can go left and right also select a range. You will like it more. 10 minute video.
 
As others have mentioned lookup should work just fine, or my preference xlookup the "never" version.

Just be mindful if you have duplicates in your initial "search for" value, the results won't be accurate.

I would probably check if there are duplicates 1st, then run the lookup.

If you have duplicates, report back as there are ways around this
This is true, as well as the dreaded #N/A when doing lookups using text values, there could have been slight data manipulation done on the one sheet e.g. padding removed, trailing spaces etc.
 
Top
Sign up to the MyBroadband newsletter