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

--
Message posted from http://www.ExcelForum.com

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=1234)*(B1:B100=DAT E(2004,9,13)),0))

--
Regards
Frank Kabel
Frankfurt, Germany


Hi,


What i am trying to do at the moment is that i have a list of
products 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 the
first one. I however need to match a paticular due date. is there
any way to do a form of an if statement:

eg to say; IF( part=1233 and due=12/09/04) then match


---
Message posted from http://www.ExcelForum.com/

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.

--
Regards,
Tom Ogilvy

"amar9876 " wrote in message
...
Hi,


What i am trying to do at the moment is that i have a list of products
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 the
first one. I however need to match a paticular due date. is there any
way to do a form of an if statement:

eg to say; IF( part=1233 and due=12/09/04) then match


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

=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=DAT E(2004,11,11)),0))
also entered with Ctrl+shift+Enter

Both worked for me in a test sheet.
--
Regards,
Tom Ogilvy


"amar9876 " wrote in message
...
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 date
11/11/04 and copy whats in colum c of this corresponding row.

thanks guys


---
Message posted from http://www.ExcelForum.com/