Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup 2 columns | Excel Worksheet Functions | |||
Lookup across columns | Excel Worksheet Functions | |||
Lookup Two Columns - Again | Excel Worksheet Functions | |||
Lookup Two Columns | Excel Worksheet Functions | |||
Lookup a value using two columns | Excel Worksheet Functions |