Cross Referencing in Excel

  B33J 13:16 15 Feb 2007


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,


  VoG II 13:47 15 Feb 2007

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 2007

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

  VoG II 14:21 15 Feb 2007

How much data is there?

  B33J 14:24 15 Feb 2007

About 50,000 entries in each sheet.

  VoG II 15:21 15 Feb 2007

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 2007

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 2007

This gets complicated. See if this helps click here

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires