If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 formula to extract specific data if match occurs
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## formula to extract specific data if match occurs

#1
February 24th 05, 01:23 AM
 jerry external usenet poster Posts: n/a
formula to extract specific data if match occurs

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

thanks

#2
February 24th 05, 02:11 AM
 Max external usenet poster Posts: n/a

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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"jerry" > wrote in message
...
> 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
>
> thanks
>

#3
February 24th 05, 11:06 AM
 Aladin Akyurek external usenet poster Posts: n/a

See for a fast formula system:

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

jerry wrote:
> 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
>
> thanks
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Extract Data From Textbox SEA Excel Worksheet Functions 6 February 5th 09 08:21 PM Extract specific data into its own workbook via macro? Adrian B Excel Discussion (Misc queries) 2 February 24th 05 06:09 AM extract data from worksheet sallu Excel Worksheet Functions 2 February 21st 05 06:50 AM match data to reference then vlookup lucky Excel Discussion (Misc queries) 0 February 8th 05 09:41 PM Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 05:55 PM

All times are GMT +1. The time now is 12:39 AM.