Cross Referencing in Excel

  B33J 13:16 15 Feb 07
Locked

Hi,

I have an Excel spreadsheet with 2 worksheets in it. I need to cross reference the first worksheet with the 2nd so that if an entry in the first matches an entry in the second I can transfer data from the 2nd worksheet corresponding to that entry into the 1st worksheet. Also it is most likely that data from the 2nd worksheet may appear more than once. Is there any quick way of being able to transfer more than one piece of data from one worksheet to the other.

Hope this makes sense.

Many thanks,

Pete

  VoG II 13:47 15 Feb 07

Let's say that you have an identifying code in column A of each sheet and that the second sheet also has info in columns B:D. Also assume that the info runs from rows 2:100 on each sheet.

To return info from Sheet2 to Sheet1

=VLOOKUP($A2, 'Sheet2'!$A$2:$D$100, 2, FALSE) for column B

=VLOOKUP($A2, 'Sheet2'!$A$2:$D$100, 3, FALSE) for column C

=VLOOKUP($A2, 'Sheet2'!$A$2:$D$100, 4, FALSE) for column D

You can then copy the formulas down as far as needed.

  B33J 14:07 15 Feb 07

I tried all that but when I press enter it says 'Not Enough Memory'. Anyway around this?

  VoG II 14:21 15 Feb 07

How much data is there?

  B33J 14:24 15 Feb 07

About 50,000 entries in each sheet.

  VoG II 15:21 15 Feb 07

The only thing I can think of is to set calculation to Manual (Tools > Options > Calculation) then enter the lookup formulas, then press F9 to calculate.

  B33J 13:52 16 Feb 07

I've managed to sort that problem out now thanks. Do you know of any way I can find multiple entries if the entry I am looking for is in the first list more than once

  VoG II 14:02 16 Feb 07

This gets complicated. See if this helps click here

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac