Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or sum product
I have a work sheet (worksheet 1) that has three columns of data and I want
to match it against another worksheet (worksheet 2) that has those same three values in one column - if there is a match then i want to return a value in column J of worksheet 2. Worksheet 1: Column D = 00001 Column G = 00000123456 Column H= 000123 Column T = Want value from worksheet 2 column J if all three above match Worksheet 2: Column I = 0000100000123456000123 Column J = 98765 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or sum product
One way ..
In Sheet1, In T2, normal ENTER: =INDEX(Sheet2!$J$2:J100,MATCH(TRUE,INDEX(D2&G2&H2= Sheet2!$I$2:$I$100,),0)) Copy down And if you need an error trap, use ISNA on the MATCH bit of it, indicatively like this: =IF(ISNA(MATCH(..)),"",INDEX(..)) aloha? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Belinda7237" wrote: I have a work sheet (worksheet 1) that has three columns of data and I want to match it against another worksheet (worksheet 2) that has those same three values in one column - if there is a match then i want to return a value in column J of worksheet 2. Worksheet 1: Column D = 00001 Column G = 00000123456 Column H= 000123 Column T = Want value from worksheet 2 column J if all three above match Worksheet 2: Column I = 0000100000123456000123 Column J = 98765 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or sum product
Getting an NA - but i think because my data isnt consistent (sometimes the
00001 is just a 1 in sheet 1 but a 000001 in sheet 2) What if I just wanted to match column G to the mid of column I (this number is always consistent) "Max" wrote: One way .. In Sheet1, In T2, normal ENTER: =INDEX(Sheet2!$J$2:J100,MATCH(TRUE,INDEX(D2&G2&H2= Sheet2!$I$2:$I$100,),0)) Copy down And if you need an error trap, use ISNA on the MATCH bit of it, indicatively like this: =IF(ISNA(MATCH(..)),"",INDEX(..)) aloha? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Belinda7237" wrote: I have a work sheet (worksheet 1) that has three columns of data and I want to match it against another worksheet (worksheet 2) that has those same three values in one column - if there is a match then i want to return a value in column J of worksheet 2. Worksheet 1: Column D = 00001 Column G = 00000123456 Column H= 000123 Column T = Want value from worksheet 2 column J if all three above match Worksheet 2: Column I = 0000100000123456000123 Column J = 98765 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or sum product
Ah, you could tinker around with the earlier expression like this,
normal ENTER to confirm the formula will do (as before): =INDEX(Sheet2!$J$2:J100,MATCH(TRUE,INDEX(G2=MID(Sh eet2!$I$2:$I$100,6,11),),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Belinda7237" wrote: Getting an NA - but i think because my data isnt consistent (sometimes the 00001 is just a 1 in sheet 1 but a 000001 in sheet 2) What if I just wanted to match column G to the mid of column I (this number is always consistent) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function
Dear evert body
i have 2 work sheet one woksheet having datas like aaa 11 12 14 12 bbb 22 22 22 22 aaa 33 23 44 55 aaa 43 23 54 23 bbb 333 333 22 111 another second worksheet is having form if i type second worksheet a1 cell aaa , i want all data from one worksheet like result aaa 11 12 14 12 33 23 44 55 43 23 54 23 Regards Manoharan -- manoharan "Belinda7237" wrote: I have a work sheet (worksheet 1) that has three columns of data and I want to match it against another worksheet (worksheet 2) that has those same three values in one column - if there is a match then i want to return a value in column J of worksheet 2. Worksheet 1: Column D = 00001 Column G = 00000123456 Column H= 000123 Column T = Want value from worksheet 2 column J if all three above match Worksheet 2: Column I = 0000100000123456000123 Column J = 98765 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function
Assuming that Sheet2!A2:E6 contains the source data, and Sheet1!A2
contains 'aaa', try the following... B2: =COUNTIF(Sheet2!A2:A6,A2) C2, confirmed with CONTROL+SHIFT+ENTER, copied across and down: =IF(ROWS(C$2:C2)<=$B$2,INDEX(Sheet2!B$2:B$6,SMALL( IF(Sheet2!$A$2:$A$6=$A$ 2,ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),ROWS(C$2:C2))),"") However, it would be more efficient to use a helper column... B2: =COUNTIF(Sheet2!A2:A6,A2) C2, confirmed with CONTROL+SHIFT+ENTER, and copied down: =IF(ROWS(C$2:C2)<=$B$2,SMALL(IF(Sheet2!$A$2:$A$6=$ A$2,ROW(Sheet2!$A$2:$A$ 6)-ROW(Sheet2!$A$2)+1),ROWS(C$2:C2)),"") D2, copied across and down: =IF($C2<"",INDEX(Sheet2!B$2:B$6,$C2),"") -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , tutymano wrote: Dear evert body i have 2 work sheet one woksheet having datas like aaa 11 12 14 12 bbb 22 22 22 22 aaa 33 23 44 55 aaa 43 23 54 23 bbb 333 333 22 111 another second worksheet is having form if i type second worksheet a1 cell aaa , i want all data from one worksheet like result aaa 11 12 14 12 33 23 44 55 43 23 54 23 Regards Manoharan -- manoharan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product Code and Product Description setup | Excel Worksheet Functions | |||
I need a product key for my Trail product, 2007 Microsoft Office s | Setting up and Configuration of Excel | |||
Using VLOOKUP to find product from 3 worksheets to put on one shee | Excel Worksheet Functions | |||
Compare Old & New Product List (i.e. VLOOKUP) | Excel Worksheet Functions | |||
VLOOKUP with PRODUCT functionality? | New Users to Excel |