Nesting Index and Match Functions

[Guest]

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?

 

[Dave Peterson]

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.

 

[Guest]

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.

 

[RagDyeR]

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!
=====================================================


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.

 

[Guest]

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

 

[Dave Peterson]

If your data isn't laid out as a nice table, then my suggestion won't work.

But RagDyeR's suggestion should.

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

 

[Guest]

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!

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!
=====================================================


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.

 

[Guest]

RagDyeR,

Thank you for responding. I tried the formula, but I am still receiving a
returned value of "#NA". Any suggestions?

--
God Bless!

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!
=====================================================


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.