Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup help
I have two worksheets. Sheet 1 contains a number in column A and a name in
column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N6-270-15 33 35 N6-270-75 N6-270-15 33 36 N6-270-110 N6-270-30 34 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup help
Novice Lee wrote:
I have two worksheets. Sheet 1 contains a number in column A and a name in column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N6-270-15 33 35 N6-270-75 N6-270-15 33 36 N6-270-110 N6-270-30 34 VLOOKUP cannot do this, because the result you want is left of the range to match (VLOOKUP requires the result to be in the same column or to the right of the range to match). But this will work in Sheet2 B1: =INDEX(Sheet1!$A$1:$A$4,MATCH($A1,Sheet1!$C$1:$C$4 ,0),1) Fill down. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup help
You can use an index/match ..
In Sheet2, Assume data in A2 down Put in B2: =IF(ISNA(MATCH(A2,Sheet1!B:B,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!B:B,0))) Copy down as far as required voila?, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Novice Lee" wrote: I have two worksheets. Sheet 1 contains a number in column A and a name in column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N6-270-15 33 35 N6-270-75 N6-270-15 33 36 N6-270-110 N6-270-30 34 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup help
Max,
Your formula needs a little treeking: Novice Lee has put the information on Sheet 1 in Columns A & C (not B). So" =IF(ISNA(MATCH(A2,Sheet1!B:B,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!B:B,0)))" Should be "=IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"",INDEX(Sheet1! A:A,MATCH(A2,Sheet1!C:C,0)))" hth "Max" wrote: You can use an index/match .. In Sheet2, Assume data in A2 down Put in B2: =IF(ISNA(MATCH(A2,Sheet1!B:B,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!B:B,0))) Copy down as far as required voila?, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Novice Lee" wrote: I have two worksheets. Sheet 1 contains a number in column A and a name in column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N6-270-15 33 35 N6-270-75 N6-270-15 33 36 N6-270-110 N6-270-30 34 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup help
Agreed, it should be:
=IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"", INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0))) Thanks for the correction -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |