Index/Match???

[Ken]

Excel 2000 ...

Col A = Part Number (Range A2:A11595)
Col B = Op No ......(Range B2:B11595)

Col M = Part Number (Range M2:M2770)
Col N = Op No ......(Range N2:M2770)
Col O = Dept No ....(Range O2:O2770)

Col D ... I need formula to search both Cols M&N for exact
match against both Cols A&B ... when match found enter
value from Col O.

Thanks again to those of you that are intimate with Excel
& support this board ... Kha

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($O$2:$O$2770,MATCH(1,($M$2:$M$2770=A2)*($N$2:$M$2770=B2),0))
and copy down

Note: I would expect that this will slow down your spreadsheet
significantly. Maybe turn off automatic calculation before entering all
these formulas

 

[Ken Wright]

Only one possible match per combo yes, and also assume dept Numbers are
numeric??

Try this in Col C:-

In C2 put = SUMPRODUCT(($M$2:$M$2770=A2)*(($N$2:$N$2770=B2)*($O$2:$O$2770))

Now copy down

If more than one match possible then the Dept Numbers will be added which will
not be what you want.

Slightly inelegant but you could also concatenate A&B into Col C temporarily,
then concatenate M&N into Col L temporarily and then just use VLOOKUP on C
against L, bringing back O. Paste the results as hardwired when done and then
clear out the temp data.

 

[RagDyer]

You could try this *array* formula:

=INDEX($M$2:$O$2770,MATCH(1,($M$2:$M$2770=A2)*($N$2:$N$2770=B2),0),3)

Which is very similar to Frank's formula, although it doesn't have the typo
that his does.<vbg>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($O$2:$O$2770,MATCH(1,($M$2:$M$2770=A2)*($N$2:$M$2770=B2),0))
and copy down

Note: I would expect that this will slow down your spreadsheet
significantly. Maybe turn off automatic calculation before entering all
these formulas

 

[Frank Kabel]

Hi RD
good spot <vbg>

 

[RagDyer]

This fixes Ken's small typo:

=SUMPRODUCT(($M$2:$M$2770=A2)*($N$2:$N$2770=B2),$O$2:$O$2770)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Only one possible match per combo yes, and also assume dept Numbers are
numeric??

Try this in Col C:-

In C2 put = SUMPRODUCT(($M$2:$M$2770=A2)*(($N$2:$N$2770=B2)*($O$2:$O$2770))

Now copy down

If more than one match possible then the Dept Numbers will be added which
will
not be what you want.

Slightly inelegant but you could also concatenate A&B into Col C
temporarily,
then concatenate M&N into Col L temporarily and then just use VLOOKUP on C
against L, bringing back O. Paste the results as hardwired when done and
then
clear out the temp data.

 

[RagDyer]

FWIW,

Frank's and my formula will work if the part numbers are numeric or text,
meaning that the part numbers may contain alpha characters.

Ken's will work only with all numeric part numbers.

I *think* that Ken's is a less demanding formula on XL, and should be used
if possible.
I'll let anyone correct me if I'm wrong.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Excel 2000 ...

Col A = Part Number (Range A2:A11595)
Col B = Op No ......(Range B2:B11595)

Col M = Part Number (Range M2:M2770)
Col N = Op No ......(Range N2:M2770)
Col O = Dept No ....(Range O2:O2770)

Col D ... I need formula to search both Cols M&N for exact
match against both Cols A&B ... when match found enter
value from Col O.

Thanks again to those of you that are intimate with Excel
& support this board ... Kha

 

[Ken Wright]

Oops - cheers for the catch RagDyer

 

[Ken Wright]

I *think* that Ken's is a less demanding formula on XL, and should be used

if possible.

But definitely subject to the caveat re more than one possible match

 

[Ken]

Ken / Rags / Frank ... many thanks for your valuable
input ... Ken/Frank ... You have helped me before ...
Rags ... you are an excellent co-pilot ...

I am always trying to us Vlookup with this situation & I
always bomb out ... I remember Frank suggesting using
Index/Match in an earlier post, but did not use it as
demands changed ... Now I have a new endeavor. So when I
am back @ work in the morning I will try your suggestions
posted hear. (Note: my PNs are alpha-numeric)

Above said ... I basicaly wanted to say "Thanks" for all
the support you provide on this board ... Kha