formula to extract specific data if match occurs

[jerry]

I am trying to build a formula in sheet A where the look up value is a check
#and its searching in sheet b. Then, i want to be able to extract all invoice
#'s and its relevant row of information paid by that check #,it could be 1 or
it could be 15.

for example

sheet A document # document date invoice#
check# 999 return value from sheet b for all these variables based
on ck#

sheet b

check# Document # document date invoice #
998 11111 12/15/03 4898
999 12345 1/1/2004 5464
999 38540 1/25/2004 6085
1000 39001 2/1/2004 6100

please help
thanks

 

[Max]

One way ..

Assuming the table below is in
In Sheet b, in cols A to D, headers in row1
--------------

check# Document # document date invoice #
998 11111 12/15/03 4898
999 12345 1/1/2004 5464
999 38540 1/25/2004 6085
1000 39001 2/1/2004 6100

Assuimg empty cols to the right,

Put in G1: ='Sheet a'!A1

Put in F2: =IF(A2="","",IF(A2=$G$1,ROW(),""))

Copy F2 down to say, F100, to cover the max expected
data in the table

In Sheet a
-------------
Cell A1 will be where you input the check#
(Input in A1: 999, say)

Copy > paste the col headers over into A2:C2, viz.:
Document # document date invoice #

Put in A3:

=IF(ISERROR(SMALL('Sheet b'!$F:$F,ROWS($A$1:A1))),"",INDEX('Sheet
b'!B:B,MATCH(SMALL('Sheet b'!$F:$F,ROWS($A$1:A1)),'Sheet b'!$F:$F,0)))

Copy across to C3, fill down to C100
(cover the same range size as in Sheet b)

Format col B ("document date") as date

For the sample data above, you'll get:

If A1 contains: 999

Document # document date i nvoice #
12345 01-01-04 5464
38540 25-01-04 6085
(rest are blanks)

(assuming date in col B are formated as "dd-mm-yy")

Changing the input in A1 to: 1000
gives:

Document # document date i nvoice #
39001 01-02-04 6100
(rest are blanks)

And so on ..

 

[Aladin Akyurek]

See for a fast formula system:

http://www.mrexcel.com/board2/viewtopic.php?t=113746