Can someone guide me please

[dhome]

Good Morning, I have two tables of data in Table 1 in column D I have 430,000 rows of data and in table 2 in column C I have 350,000 rows of data. completing a Lookup is crashing the computer, Can anyone in the room offer any alternative formula please?

 

[etaf]

what formula are you using
What version of Excel
how are you referencing the cells

=index( $D$2:$D$500000, match( C2 , $A$2:$A$500000,0))

what is your formula - post here
maybe a sample using XL2BB - see menu or signature

 

[dhome]

what formula are you using
What version of Excel
how are you referencing the cells

=index( $D$2:$D$500000, match( C2 , $A$2:$A$500000,0))

what is your formula - post here
maybe a sample using XL2BB - see menu or signature

Hi
My formula was a simple VLOOK UP =VLOOKUP(A2,'list'!E2:E437000,5,FALSE)

There are three tabs
MASTER LIST
LIST
NET

Simply looking to fill column F in Master list with data from the List tab column F and column G in Master list with data from the Net tab column C
The issue is the LOOKUP keeps crashing as I have 500,000 rows of data in the Master List Tab.

Using Windows 10
Thank you

 

[etaf]

that will not work
=VLOOKUP(A2,'list'!E2:E437000,5,FALSE)

you are chosing column 5 from E
so E,F,G,H,J
column J
that needs to be included in the range
=VLOOKUP(A2,'list'!E2:J437000,5,FALSE)

 

[dhome]

that will not work
=VLOOKUP(A2,'list'!E2:E437000,5,FALSE)

you are chosing column 5 from E
so E,F,G,H,J
column J
that needs to be included in the range
=VLOOKUP(A2,'list'!E2:J437000,5,FALSE)

It wont work full stop, that was a typo by me, I cannot use VLOOKUP to solve this issue, It crashes computer, MATCH MAY WORK do you have any suggestions, or Power Tools?
Thank you

 

[dhome]

Can i not use Index, Match

 

[severynm]

If a VLOOKUP 440000 rows causes excel to crash, an INDEXMATCH will do no better (see this article: VLOOKUP in Excel - which formula is fastest?). Format your data as an Excel table and see if that increases the evaluation speed of your lookup formula.

 

[etaf]

Can i not use Index, Match

for completeness this is the index/match - if others are searching
as [B]severynm[/B] has posted , it may not solve the computer crash issue

=VLOOKUP(A2,'list'!E2:J437000,5,FALSE)

=Index($J$2:$J$437000, match ( A2, $E$2:$E$437000,0 ))

if you wanted to try

 

[dhome]

for completeness this is the index/match - if others are searching
as [B]severynm[/B] has posted , it may not solve the computer crash issue

=VLOOKUP(A2,'list'!E2:J437000,5,FALSE)

=Index($J$2:$J$437000, match ( A2, $E$2:$E$437000,0 ))

if you wanted to try

how doe Index MATCH work when I am taking information from different tabs, there is no link to the
There are three tabs
MASTER LIST
LIST
NET

 

[etaf]

=Index(list!$J$2:$J$437000, match ( A2, list!$E$2:$E$437000,0 ))
Sorry missed out the sheetname, you just use the same range as vlookup() with index/match
no idea how you have structed the lookup to work with 3 different tabs, all i was doing was replacing the formula you poste d
BUT before getting into the 3 tabs, if the
=Index(list!$J$2:$J$437000, match ( A2, list!$E$2:$E$437000,0 ))
crashes the computer - then not sure how to improve
I dont have any of the power tools as i'm on a Mac using OSX excel, which does NOT have those tools
Also I nolonger provide VBA solutions as the last time i used was late 90's early 00's

or addressing the full column
=INDEX(List!$J:$J,MATCH(A2,List!$E:$E,0))

I dont have all you data loaded and also not sure on the spec of your PC

In the past (1990's) even with a then leading spec laptop, i had issues and had to convert a lot of spreadsheets into Access - which i also had , as had office Pro versions
Again , cant help - 365 for Mac does not include Access