vlookupmatch 2 criteria

  • Thread starter Thread starter amar9876
  • Start date Start date
A

amar9876

Hi,


What i am trying to do at the moment is that i have a list of product
and also the dates when they are due : ie

PRODID DUE

1233 12/09/04
1233 13/09/04
1233 14/09/04

when matcing a part using the vlookup function it only matches th
first one. I however need to match a paticular due date. is there an
way to do a form of an if statement:

eg to say; IF( part=1233 and due=12/09/04) then matc
 
Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=1234)*(B1:B100=DATE(2004,9,13)),0))
 
You can use an array formula

=MATCH("123313/09/04",A1:A6&TEXT(B1:B6,"dd/mm/yy"),0)

will give you the row. the formula must be entered with Ctrl+Shift+Enter

you can combine this with Index to return a value in another column

=Index(D1:D6,MATCH("123313/09/04",A1:A6&TEXT(B1:B6,"dd/mm/yy"),0),1)

again, entered with Ctrl+Shift+enter.
 
still cant get them to work

say ive got


A B c

1 1123 10/11/04 11


2 1123 11/11/04 12


3 1123 12/11/04 13

in box d1 say i want to search the list for item 1123 with dat
11/11/04 and copy whats in colum c of this corresponding row.

thanks guy
 
=Index(C1:C6,MATCH("112311/11/04",A1:A6&TEXT(B1:B6,"dd/mm/yy"),0),1)

would work. Entered with Ctrl+Shift+Enter rather than just enter. I
assume you haven't entered it with Ctrl+shift+enter

this assumes that column A contains numbers and column B actually holds
Excel date values.

Frank's would be:

=INDEX(C1:C100,MATCH(1,(A1:A100=1123)*(B1:B100=DATE(2004,11,11)),0))
also entered with Ctrl+shift+Enter

Both worked for me in a test sheet.
 
Back
Top