lookup over several columns

sandernagtegaal

unread,

Jan 7, 2010, 9:25:07 AM1/7/10

to

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/aspnet/aca2f3eb-dab6-47bd-a84c-920b6365063b/c-specification-compiled.aspx

Bernard Liengme

unread,

Jan 7, 2010, 9:59:24 AM1/7/10

to

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 news:201017925...@scarlet.be...

sandernagtegaal

unread,

Jan 8, 2010, 3:12:48 AM1/8/10

to

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/aspnet/f8936cf5-711a-46bf-9f25-924302fe7151/build-a-google-search-vs.aspx