LOOKUP INFO MUST PASS 2 CRITERIA

[sandyw]

Think of vlookup. You tell it what you are matching and it returns a value.
I need it to match 2 criteria, not 1, to return a value.

Example:

Part NO. Ship Date Tracking #
1234 6/5/04 11111
1234 7/2/04 22222

If I use the vlookup to pull the tracking # based on part # I will always
get 11111.

I need to be able to say, give me the tracking # when the sales order is
1234 AND the ship date is 7/2/04.

Any suggestions?

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=1234)*(B1:B100=DATE(2004,7,2)),0))

 

[sandyw]

I am using rc where you have 1234 and rc where you have "date" and it is not
working. What am I not getting?

Also what do you mean entered with ctr+shift+enter?

 

[Frank Kabel]

Hi
not sure what you mean with 'rc'. But CTRL+ENTER+SHIFT
just means that you have to finish the formula entry with
these keys instead of a single eNTER. This will indicate
that yiou enter an array formula. Excel will place curly
brackets around the formula

 

[Biff]

Here's a non-array alternative as long as the tracking
number is in fact just a string of numbers and each
tracking number is unique:

A1 = part no.
B1 = ship date

=SUMPRODUCT(--(A5:A500=A1),--(B5:B500=B1),C5:C500)

Biff