In Excel can you do Vlookup accros 2 files and display it as a value

[KatyMac]

So table on one file
& I want the values (not the formula) in a second file?

Or do have have to copy the table across and then can I delete it once the vales are in place?

Or is there a better way to do it?

Do the vlookup. Once you get the results do copy, paste special, values only.

The existing table is in date order with no gaps, name, formula1, formula 2, formula3 & formula4

The new table is in date order with lots of spaces/rows for other data
name, data, data, field1, field 2, field3,filed4
othername, data, data, data, data, data, data
othername, data, data, data, data, data, data
othername, data, data, data, data, data, data
name, data, data, field1, field 2, field3, field4

I'm trying to avoid copy&paste on each line - am I missing a short cut

You can drag the formula down each row to get the values then copy and paste special. That is if I have understood you correctly.

possibly you are missing a trick or 2.

you can change the column your lookup is referencing by changing the number in the formula your write. For example. In new table type

=VLOOKUP(A2,"oldtable1,A2:G500,1,false)

returns an exact match for column 1 in the table you are looking for the value of A2 in. If you change the "1" for a "2" the same is repeated but for the next column along etc.

You might benefit from using $ (dollar signs) in your formula to create an absolute reference for part of it. This means one that doesn't "move" when you copy the formula to a new cell.

It's about 300 rows spread over a thousand or so

I wanted it to look up a name in the existing field and input a value from the existing field into a new field & then copy and paste that all the way down the table, so it only returns data when there is the same name in both fields?

Maybe you can't do it all at once

You can do a lookup from another workbook but you’d have the lookup formula in the table (displayed as a value obviously) not just a value.

You’d need to copy and paste special after you did the lookup if you want rid of the formula.

So

=VLOOKUP(A$,"oldtable1,A2:G500,1,false)

does 'oldtable1' include the file address or should I copy it across and do it from the same file

exceljet.net/formula/vlookup-from-another-workbook

No oldtable1 won’t include the file address. You need to be explicit.

Personally, if you can, I'd copy across first. This means that when you write your lookup you are referencing a different sheet in the same workbook, not a whole new filename/workbook, which means the formula is shorter and easier to follow.

The order of things is generally, write lookup, open bracket, then click for value you want to search for (comma) then select the range you want to search in, by click and drag, so the search range should "write itself" you shouldn't need to type it out.

Hope that's helpful

First off, thanks for everyone's help

By trying things I am clarifying my thinking

So I have data - but in the fields that there is no data I have #N/A is there a way of getting a zero/emptyfield or a dash?

Ctrl H replace #N/A, then leave the replace part blank

You need to wrap your lookup in an iferror to replace your N/As

=IFERROR(VLOOKUP(YOURLOOKUP),0)

Make sure you don't move either file, and don't rename them.

So the links will still work.

Yes you end up with a redundant sheet - shame you can't hide it or lock it

But never mind - won't make that mistake again!