lookup over several columns

[Sander Nagtegaal]

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/tutorial...4c-920b6365063b/c-specification-compiled.aspx

 

[Bernard Liengme]

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

 

[Sander Nagtegaal]

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) VLOOKU
On Sheet1 make a new A colum
In A1 use =B1&C1 and copy down the column by double clicking A1's fil
handl
In D1 of Sheet 2 enter =VLOOKUP(A1&B1,Sheet1!$A$1:$D$2000,4,FALSE
Copy down the colum

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

best wishe
-
Bernard Liengm
Microsoft Excel MV
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/tutorial...25-924302fe7151/build-a-google-search-vs.aspx