lookup over several columns
 

I'm trying to have a lookup over several columns

situation
sheet 1
A B C
56 110 50000
56 120 10000
56 130 5000
57 110 30000
57 120 10000
57 130 5000

sheet 2
A B C D
56 110 0000
56 110 0001
56 120 0000
56 120 0001

I need value C from sheet 1 put into column D in sheet 2 no matter wath the value is in column C of sheet 2

How do I do this (exact file is 25000 lines so copy/paste isn't a real option)


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# Specification Compiled CHM Help File
http://www.eggheadcafe.com/tutorials...-compiled.aspx

lookup over several columns
 

Two ideas:
A) VLOOKUP
On Sheet1 make a new A column
In A1 use =B1&C1 and copy down the column by double clicking A1's fill
handle
In D1 of Sheet 2 enter =VLOOKUP(A1&B1,Sheet1!$A$1:$D$2000,4,FALSE)
Copy down the column

B) INDEX & MATCH
In row 1 of some column to the right (I will use K) =A1&B1
Copy down the column by dragging
In D1 of Sheet2 enter
=INDEX(Sheet1!C1:C2000,MATCH(A1&B1,Sheet1!$K$1:$K$ 2000,0))
Copy down the column

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"Sander Nagtegaal" wrote in message ...
I'm trying to have a lookup over several columns

situation
sheet 1
A B C
56 110 50000
56 120 10000
56 130 5000
57 110 30000
57 120 10000
57 130 5000

sheet 2
A B C D
56 110 0000
56 110 0001
56 120 0000
56 120 0001

I need value C from sheet 1 put into column D in sheet 2 no matter wath
the value is in column C of sheet 2

How do I do this (exact file is 25000 lines so copy/paste isn't a real
option)


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# Specification Compiled CHM Help File
http://www.eggheadcafe.com/tutorials...-compiled.aspx

Thanks
 

Thank you very much for the quick reply



Bernard Liengme wrote:

Two ideas:A) VLOOKUPOn Sheet1 make a new A columnIn A1 use =B1&C1 and copy
07-Jan-10

Two ideas:
A) VLOOKUP
On Sheet1 make a new A column
In A1 use =B1&C1 and copy down the column by double clicking A1's fill
handle
In D1 of Sheet 2 enter =VLOOKUP(A1&B1,Sheet1!$A$1:$D$2000,4,FALSE)
Copy down the column

B) INDEX & MATCH
In row 1 of some column to the right (I will use K) =A1&B1
Copy down the column by dragging
In D1 of Sheet2 enter
=INDEX(Sheet1!C1:C2000,MATCH(A1&B1,Sheet1!$K$1:$K$ 2000,0))
Copy down the column

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Google Search VS.Net IDE Macro
http://www.eggheadcafe.com/tutorials...search-vs.aspx