#1   Report Post  
Posted to microsoft.public.excel.misc
eec eec is offline
external usenet poster
 
Posts: 6
Default index/if function

A B C D E F G H I
Acct# Inv# Date Amt Sales Total Alloc Alloc Alloc
Tax Amt
Amt S.Tax Total
68847 121508 1/15/09 $2,358 $2,358 $0 $0 $0
68847 011509 2/17/09 $1,470 $30 $1,500 $0 $0 $0
75181 110509 12/7/09 $890 $10 $900 $0 $0 $0
9201(II) 1209/75 5/12/09 $1,200 $0 $1,200 $672 $0 $672
9201(III) 1209/75 5/12/09 $528 $0 $528 $0 $0 $0

Invoice data is on Sheet2, on Sheet1 I need to pull information based on
invoice # to submit with the payment of the invoice. I need to allocate some
invoices based on invoice number as we receive 1 invoice but it is paid by
different divisions.

The following array formula works pulling most data I need except the amount
because I need it to pull from column I if it has data and column F if column
I is = 0.

Sheet1 cell A1 is the invoice number I want to pull data for. Sheet1 cell
A2 is a count if function to total occurances of that invoice #.

=IF(ROWS(A$3:A3)$A$2,
,Index(Sheet2!F:F,SMALL(IF(Sheet2!B:B=$A$1,ROW( Sheet2!F:F)),ROW(1:1))))

I need to manipulate this formula or come up with a whole new one that pulls
column I if there is data there and column F if column I = 0.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index function JP Ronse Excel Worksheet Functions 4 August 30th 09 04:07 PM
Using INDIRECT Function and INDEX Function ChristopherG Excel Discussion (Misc queries) 1 June 10th 09 04:07 PM
INDEX FUNCTION Gulfman100 New Users to Excel 6 April 11th 08 12:51 PM
Index Function/Match Function M Moore Excel Discussion (Misc queries) 3 September 3rd 06 11:49 AM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"