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!

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

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
>

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" <[email protected]> wrote in message
news:[email protected]...
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
>

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
>

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

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" <[email protected]> wrote in message
> news:[email protected]...
> 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
> >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> 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
> >
>
>
>