| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Index and Match Functions
I have 2 worksheets that I need to compare "Part No" and "LCN" to return the
"PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
So part numbers go down (say) column A and LCN's go across (say) row 1.
If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
No. All of the data is in columns. I need to pull the "PLISN" from
Worksheet "ALSTAR" into Worksheet "SLIC" ased on a match of data in column "A" and "B". Both worksheets contain the same data columns (ie, Part No, LCN, PLISN). Example: "A" "B" "C" Part No. LCN PLISN 4116-2 AFY BFGC 5678 AFC AFER ETC. -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
Try this *array* formula in C2 of SLIC:
=INDEX(ALSTAR!$C$2:$C$100,MATCH(1,(ALSTAR!$A$2:$A$ 100=A2)*(ALSTAR!$B$2:$B$10 0=B2),0)) And copy down as needed. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Malone" wrote in message ... No. All of the data is in columns. I need to pull the "PLISN" from Worksheet "ALSTAR" into Worksheet "SLIC" ased on a match of data in column "A" and "B". Both worksheets contain the same data columns (ie, Part No, LCN, PLISN). Example: "A" "B" "C" Part No. LCN PLISN 4116-2 AFY BFGC 5678 AFC AFER ETC. -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
Dave,
I tried the link you sent, and I tried one of the sample formulas from that web site, but I am still getting "#NA". The follwing is the formula I am using =INDEX(ALSTAR!$C$2:$C$49878,MATCH(TEXT(B19,"00000" ),ALSTAR!$A$2:$A$49878,0),MATCH(TEXT(E19,"00000"), ALSTAR!$D$2:$D$39999,0)) I am using the "TEXT" function because there is a mixture of text and numbers in the data. I want the "PLISN" from the "ALSTAR" worksheet to appear in the "SLIC" worksheet in the column "ALSTAR PLISN". A sample of the data follows: SLIC Worksheet (B) (E) (F) (G) (J) (K) SLIC ALSTAR Ref. No.* Provn. LCN* Provn. ALC* LCN Type* PLISN PLISN 4116-337 AFALCASY 1 P EHMA 24A55B AFALCANY 0 P DVTA M6325 AFZ 0 P ZKFA AN833-6D AFAEACRY 0 P BQFA 24-00157-823 AFALCAFY 3 P FJMA 24-00157-823 AFALEAAY 3 P HGDA 24-00157-823 AFANAAREY 0 P JPPA 24-00157-823 AFASGY 0 P KZSA ALSTAR Worksheet (A) (B) (C) (D) REFN PCCN PLISN LCN 13347750 ELBACE AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBACE AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBDUM AAAA AF -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
If your data isn't laid out as a nice table, then my suggestion won't work.
But RagDyeR's suggestion should. Malone wrote: Dave, I tried the link you sent, and I tried one of the sample formulas from that web site, but I am still getting "#NA". The follwing is the formula I am using =INDEX(ALSTAR!$C$2:$C$49878,MATCH(TEXT(B19,"00000" ),ALSTAR!$A$2:$A$49878,0),MATCH(TEXT(E19,"00000"), ALSTAR!$D$2:$D$39999,0)) I am using the "TEXT" function because there is a mixture of text and numbers in the data. I want the "PLISN" from the "ALSTAR" worksheet to appear in the "SLIC" worksheet in the column "ALSTAR PLISN". A sample of the data follows: SLIC Worksheet (B) (E) (F) (G) (J) (K) SLIC ALSTAR Ref. No.* Provn. LCN* Provn. ALC* LCN Type* PLISN PLISN 4116-337 AFALCASY 1 P EHMA 24A55B AFALCANY 0 P DVTA M6325 AFZ 0 P ZKFA AN833-6D AFAEACRY 0 P BQFA 24-00157-823 AFALCAFY 3 P FJMA 24-00157-823 AFALEAAY 3 P HGDA 24-00157-823 AFANAAREY 0 P JPPA 24-00157-823 AFASGY 0 P KZSA ALSTAR Worksheet (A) (B) (C) (D) REFN PCCN PLISN LCN 13347750 ELBACE AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBACE AAAA AF 13347750 ELBDUM AAAA AF 13347750 ELBDUM AAAA AF -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
Dear RagDyeR,
I went back and used the formula in a different cell, and it worked! Praise the Lord. Thank you so much for helping. -- God Bless! "RagDyeR" wrote: Try this *array* formula in C2 of SLIC: =INDEX(ALSTAR!$C$2:$C$100,MATCH(1,(ALSTAR!$A$2:$A$ 100=A2)*(ALSTAR!$B$2:$B$10 0=B2),0)) And copy down as needed. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Malone" wrote in message ... No. All of the data is in columns. I need to pull the "PLISN" from Worksheet "ALSTAR" into Worksheet "SLIC" ased on a match of data in column "A" and "B". Both worksheets contain the same data columns (ie, Part No, LCN, PLISN). Example: "A" "B" "C" Part No. LCN PLISN 4116-2 AFY BFGC 5678 AFC AFER ETC. -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nesting Index and Match Functions
RagDyeR,
Thank you for responding. I tried the formula, but I am still receiving a returned value of "#NA". Any suggestions? -- God Bless! "RagDyeR" wrote: Try this *array* formula in C2 of SLIC: =INDEX(ALSTAR!$C$2:$C$100,MATCH(1,(ALSTAR!$A$2:$A$ 100=A2)*(ALSTAR!$B$2:$B$10 0=B2),0)) And copy down as needed. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Malone" wrote in message ... No. All of the data is in columns. I need to pull the "PLISN" from Worksheet "ALSTAR" into Worksheet "SLIC" ased on a match of data in column "A" and "B". Both worksheets contain the same data columns (ie, Part No, LCN, PLISN). Example: "A" "B" "C" Part No. LCN PLISN 4116-2 AFY BFGC 5678 AFC AFER ETC. -- God Bless! "Dave Peterson" wrote: So part numbers go down (say) column A and LCN's go across (say) row 1. If yes, then take a look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions03.html She's got a few examples there. Malone wrote: I have 2 worksheets that I need to compare "Part No" and "LCN" to return the "PLSIN" associated with the match between the worksheets of the two data points. Can anyone provide guidance? -- God Bless! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help Please - Match & Index Functions (I hope)! | Excel Discussion (Misc queries) | |||
| Index and match functions help needed. | Excel Worksheet Functions | |||
| Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
| How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Linear Mode
