Index and Match 05-10-10

Hello All,

This is on Excel 2007
I would like to see if the array formula below can be changed to check for 
another criteria.
Want this currently does is finds all occurances of cell AL527(despatch 
note) and matches that with the sheet "Data" col N (despatch note) and the 
result is taken from sheet "Data" col E (qty). If no match found then the 
result is ""
The reason the ROW 3 at the end has $ is it helps with copying the formula 
as I am using it for Columns rather than rows, I could not find a formula 
that worked for colums.

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")}


I now want to find all occurances of cell AL527 as before but only if the 
value in cell BR527 is matched with a value from sheet "Data" col S

This array formula is in cell BR527

AL527(despatch note), Col N (despatch note), Col S (period)

{=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDEX(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0)))}


Is it possible to combine the 2 formula's together?
I have been trying but cant even get a formula to be accepted :-(

Thanks
Winnie

0
Utf
5/10/2010 8:44:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
814 Views

Similar Articles

[PageSpeed] 57

Attempting to summarise:

Your first formula takes a value from column E at the same row as the third 
occurrence of AL527 within column N or returns blank if there is no third 
occurrence; and your second formula takes a value from column S at the same 
row as the first occurrence of AL527 within column N or returns blank if it 
doesn't occur.

So, you want a single formula that takes a value from column E at the same 
row as the third occurrence of AL527 within column N, but only if...  <and 
then the summary kind of falls apart, because the first could only work if 
the second were true anyway>

Can you phrase your overall requirements in a similar sentence?




"winnie123" <winnie123@discussions.microsoft.com> wrote in message 
news:B410DF15-0B93-43F9-9996-A94AE690D9D6@microsoft.com...
> Hello All,
>
> This is on Excel 2007
> I would like to see if the array formula below can be changed to check for
> another criteria.
> Want this currently does is finds all occurances of cell AL527(despatch
> note) and matches that with the sheet "Data" col N (despatch note) and the
> result is taken from sheet "Data" col E (qty). If no match found then the
> result is ""
> The reason the ROW 3 at the end has $ is it helps with copying the formula
> as I am using it for Columns rather than rows, I could not find a formula
> that worked for colums.
>
> {=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")}
>
>
> I now want to find all occurances of cell AL527 as before but only if the
> value in cell BR527 is matched with a value from sheet "Data" col S
>
> This array formula is in cell BR527
>
> AL527(despatch note), Col N (despatch note), Col S (period)
>
> {=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDEX(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0)))}
>
>
> Is it possible to combine the 2 formula's together?
> I have been trying but cant even get a formula to be accepted :-(
>
> Thanks
> Winnie
> 

0
Steve
5/11/2010 1:41:21 PM
Sorry Steve,

It was late last night when I posted this.

I only want to do the calculation if on the sheet "Data" Column S has the 
same value as Column BR on my other sheet

If BR257 = "Data" Column S(whatever row that is in then

{=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")} 

Does that make anymore sense?
Winnie
 
"Steve Dunn" wrote:

> Attempting to summarise:
> 
> Your first formula takes a value from column E at the same row as the third 
> occurrence of AL527 within column N or returns blank if there is no third 
> occurrence; and your second formula takes a value from column S at the same 
> row as the first occurrence of AL527 within column N or returns blank if it 
> doesn't occur.
> 
> So, you want a single formula that takes a value from column E at the same 
> row as the third occurrence of AL527 within column N, but only if...  <and 
> then the summary kind of falls apart, because the first could only work if 
> the second were true anyway>
> 
> Can you phrase your overall requirements in a similar sentence?
> 
> 
> 
> 
> "winnie123" <winnie123@discussions.microsoft.com> wrote in message 
> news:B410DF15-0B93-43F9-9996-A94AE690D9D6@microsoft.com...
> > Hello All,
> >
> > This is on Excel 2007
> > I would like to see if the array formula below can be changed to check for
> > another criteria.
> > Want this currently does is finds all occurances of cell AL527(despatch
> > note) and matches that with the sheet "Data" col N (despatch note) and the
> > result is taken from sheet "Data" col E (qty). If no match found then the
> > result is ""
> > The reason the ROW 3 at the end has $ is it helps with copying the formula
> > as I am using it for Columns rather than rows, I could not find a formula
> > that worked for colums.
> >
> > {=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")}
> >
> >
> > I now want to find all occurances of cell AL527 as before but only if the
> > value in cell BR527 is matched with a value from sheet "Data" col S
> >
> > This array formula is in cell BR527
> >
> > AL527(despatch note), Col N (despatch note), Col S (period)
> >
> > {=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDEX(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0)))}
> >
> >
> > Is it possible to combine the 2 formula's together?
> > I have been trying but cant even get a formula to be accepted :-(
> >
> > Thanks
> > Winnie
> > 
> 
0
Utf
5/11/2010 5:52:01 PM
I'm reluctant to give this formula, because I still think clarification is 
required, but this matches what you've requested:

=IFERROR(IF(MATCH($BR$257,Data!$S$1:$S$500,0),INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),3)))),"")



"winnie123" <winnie123@discussions.microsoft.com> wrote in message 
news:632F97A9-D111-4B75-B542-5D2F455C9464@microsoft.com...
> Sorry Steve,
>
> It was late last night when I posted this.
>
> I only want to do the calculation if on the sheet "Data" Column S has the
> same value as Column BR on my other sheet
>
> If BR257 = "Data" Column S(whatever row that is in then
>
> {=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")}
>
> Does that make anymore sense?
> Winnie
>
> "Steve Dunn" wrote:
>
>> Attempting to summarise:
>>
>> Your first formula takes a value from column E at the same row as the 
>> third
>> occurrence of AL527 within column N or returns blank if there is no third
>> occurrence; and your second formula takes a value from column S at the 
>> same
>> row as the first occurrence of AL527 within column N or returns blank if 
>> it
>> doesn't occur.
>>
>> So, you want a single formula that takes a value from column E at the 
>> same
>> row as the third occurrence of AL527 within column N, but only if... 
>> <and
>> then the summary kind of falls apart, because the first could only work 
>> if
>> the second were true anyway>
>>
>> Can you phrase your overall requirements in a similar sentence?
>>
>>
>>
>>
>> "winnie123" <winnie123@discussions.microsoft.com> wrote in message
>> news:B410DF15-0B93-43F9-9996-A94AE690D9D6@microsoft.com...
>> > Hello All,
>> >
>> > This is on Excel 2007
>> > I would like to see if the array formula below can be changed to check 
>> > for
>> > another criteria.
>> > Want this currently does is finds all occurances of cell AL527(despatch
>> > note) and matches that with the sheet "Data" col N (despatch note) and 
>> > the
>> > result is taken from sheet "Data" col E (qty). If no match found then 
>> > the
>> > result is ""
>> > The reason the ROW 3 at the end has $ is it helps with copying the 
>> > formula
>> > as I am using it for Columns rather than rows, I could not find a 
>> > formula
>> > that worked for colums.
>> >
>> > {=IFERROR(INDEX(Data!$E$1:$E$500,SMALL(IF(Data!$N$1:$N$500=$AL527,ROW(Data!$N$1:$N$500)),ROW($3:$3))),"")}
>> >
>> >
>> > I now want to find all occurances of cell AL527 as before but only if 
>> > the
>> > value in cell BR527 is matched with a value from sheet "Data" col S
>> >
>> > This array formula is in cell BR527
>> >
>> > AL527(despatch note), Col N (despatch note), Col S (period)
>> >
>> > {=IF(ISNA(MATCH(AL527,Data!$N$2:$N$500,0)),"",INDEX(Data!$S$2:$S$500,MATCH(AL527,Data!$N$2:$N$500,0)))}
>> >
>> >
>> > Is it possible to combine the 2 formula's together?
>> > I have been trying but cant even get a formula to be accepted :-(
>> >
>> > Thanks
>> > Winnie
>> >
>> 

0
Steve
5/11/2010 8:21:40 PM
Reply:

Similar Artilces:

Windows mail problem 07-16-10
Tried to log into my optonline account and received the following message: The connection to the server has failed. Account: 'mail.optonline.net', Server: 'mail.optonline.net', Protocol: POP3, Port: 995, Secure(SSL): Yes, Socket Error: 10060, Error Number: 0x800CCC0E Can anyone decipher this for me? What is the issue? You should not be using advanced ports or SSL, see http://optimum.custhelp.com/cgi-bin/optimum.cfg/php/enduser/std_adp.php?p_faqid=2397&p_created=1237397986&p_sid=ZbGnw*4k&p_accessibility=0&p_redirect=&p_lva=&p_sp=cF9zcmNoPSZ...

MATCH, LOOKUP, macro?
I have a list of 176 items and another list of 870 items. I need to see if the items in the smaller list are on the bigger list. There is no guarentee of any ascending or descending sort order. And I need to match two values in the same row. So: IF (A2 is in B2:B871) _ AND **in the same row** C2 matches the value in col D _ THEN return "Yes" MATCH and LOOKUP seem to require a sort order. And I'm not sure how to use the row number with those. I could cobble together a macro, but I thought a built-in function might be faster and easier (not to mention I might actuall...

RESOURCE USAGE 03-21-10
I work with a calendar that is setting 9 hours work/day for all the resources. In the resource usage I see that more than 8 hours of work is shown in red, but also sometimes all kinds of hours like 4.5, 5.6, 4.8 hs is red as well, even though is a normal working day. What is going on here???? txs A couple of things: 1) Are you sure the resource calendars are also set to 9 hours per day? If not, they would show up overallocated when assigned to 9 hours of tasks. 2) Project calculates overallocations to the minute. If you have two tasks, both assigned to the same resource...

Windows installer problem 04-08-10
Hi All, After login to windows xp, on screen displayed windows installer and nothing showing on screen .Any reply will be appreciated. Best regards Anji Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2) when posting in a forum or newsgroup. Please do so in your next reply. Was the computer fully patched at Windows Update when this problem started? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defender)? What third-party firewall (if any)? Has a(another)...

$10,000 a week, as much as $40,000 a month or even up to $500,000 a year!
1) Though multi-level Marketing has been near and dear to many for more than 50 years, 95% of those who trusted it have failed. 2) They failed because most people simply cannot close sales. Without closing they cannot recruit. 3) Without recruiting, duplication cannot happen. Even the 5% who CAN recruit cannot achieve their full potential without duplication. 4) None of the hype, recruiting systems, replicated websites, interruption marketing, sexy products, exotic compensation plans, training kits, books, tapes, seminars, conference calls, or "heavy hitters" have been able to...

POP
An invoice was matched to a shipment and the user deleted the invoice. Now when she attempts to re-match the invoice to the shipment, nothing is showing up in the Auto-Invoice or Match to Shipment screens. Can someone please help me re-open this Shipment for Invoicing or let me know some other work-around. Thanks, jav, Are you 100% sure there's nothing there? When the invoice was deleted, it should have made the shipment available again. Was the invoice saved in a batch and then deleted before the batch was posted? Or was the invoice posted and then somehow deleted? Frank Hamel...

Exchange 5.5 #10
I followed the article suggested and identified the last committed log. When I go to the transaction log folder, however, there are no back logs to move. The only logs in the folder are edb.log, edb0264D.log (last known committed log), res1.log and res2.log. The article says not to move the edb.log, res1.log or res2.log, just move any logs older than the last one committed, but there aren't any. Any more suggestions? Thanks! ...

Formula Help 02-02-10
How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

excel sorting 03-28-10
I have a text spreadsheet of 5,000 names and addresses. Column B needs to restricted to only 16 characters in that column. How do I send the records to the bottom of the page (or delete them) that have data that exceeds 16 characters in Column B? You could insert a new column. In that column, in row 2 put the formula =len(B2) and copy it down the column. Then sort the table on then new column ascending. Tom ...

Help does not allow entry in Answer Wizard or Index tab
Hello -- I am using Office 2000 Pro; Excel version is 9.0.6976 SP-3.. If I click a word in VBA, Help displays a help screen about it. However, the top menu bar says "Microsoft Access Help". I have Access on the machine. And, if I click the Answer Wizard or Index tab, the shape of the cursor in any field changes to a horizontal, double-ended arrow. Can anyone tell he how to solve this problem? Thanks for any help. L Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Date...

Matching Problem
Hello i have a querying problem that i need to solve. i have to sets of parts. Valves and Repair Kits. there is a manufacture part number for both that i can match on. the problem is that i know that there are valves in my list that dont have repair kits. and i know that there are repair kits that dont have matching valves. i want to create a list that shows all of the matches as well as both the valves that dont have repair kits in my list and repair kits that dont have valves. both are stored in one table and i have 2 seperate queries that show all the valves that have repair kits...

Credit card processing 05-15-07
Is it possible with Access? Any good references? Matt <mdw233psu@yahoo.com> wrote: >Is it possible with Access? Not without putting your data in a SQL Server database among many other things. The credit card companies want to ensure your corporation is keeping that data very, very secure. And depending on the laws in your province, state, territory and/or country your employer really wants to keep that data secure. It is very embarrassing to hear your corp name on the news. >Any good references? No idea. Tony -- Tony Toews, Microsoft Access MVP Please respond only i...

ploting one cell from 10 worksheets into one chart
i am trying to figure out a way to plot one cell, lets say c-1 from 10 -30 worksheets, in one workbook; on one chart. Each worksheet is a date and each c-1 value is just a number. any help would help thanks alex n. alex.nazarchuK@unitedwater.com ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ You can plot each data point as a separate ser...

Macro to delete rows 03-17-10
I'd appreciate some help in creating a macro to delete rows. Here's what I need: 1) Delete rows 1-13 2) Then find all rows that are highlited blue and delete them 3) Then find all rows that include the text "Distr" and delete them Suggestions? Thanks in advance. Tell us how the rows came to be blue... -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "JeffF" <JeffF@discussions.microsoft.com> wrote in message news:863D165C-D100-42E5-9294-3569F1829DF0@microsoft.com... > I'd appreciate some help in creating a...

SQL Command match Items to Matrix
SQL command that will put all items which contain a certain Supplier ID # to be added to a matrix that I have created. We have a company and we want to do a matrix for all of there items. But instead of manually adding each item to the matrix. If I could run an SQL command that will take all items with the supplier ID attached to them and have them added to the matrix. I am aware that I will still have to label the dimensions. It would just be fantastic if I could do a mass add. Hopefully someone has a creative mind. I tried some commands, but I am not an SQL master either. I know that ...

Customisation 08-15-05
Hi, We have been running CRM for several months now on standard licenses. We have recently used up the 8000 byte limit for entities in accounts so have decided to upgrade to professional licenses and use the orders and contracts functionality. However we have a few thousand accounts that need orders created for each product they have (currently have a tab each on accounts information page). Is there any way of automating this to create a new order for each product each account has? Also is there any way of changing the "name" field on an order to be a picklist? or could i cr...

Creating Reports 05-08-07
I have been investigating the different methods of creating a new report in crm and have found a few different methods. What is the easier and time efficient method for creating a new custom report? On May 8, 3:26 pm, Nate <N...@discussions.microsoft.com> wrote: > I have been investigating the different methods of creating a new report in > crm and have found a few different methods. What is the easier and time > efficient method for creating a new custom report? Pre-filtering the reports per the SDK is probably going to be your best bet. You can upload them into CRM and ...

Stats microsoft.public.windowsxp.general (last 7 days) 06-07-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 07 Jun 2010 08:14:28 GMT Stats Begin...............: Mon, 31 May 2010 08:29:11 GMT Stats End.................: Mon, 07 Jun 2010 06:42:42 GMT Days......................: 7 Total No. of Articles.....: 526 Total No. of Characters...: 1263268 Total Volume..............: 1233 Messages Per Day..........: 75.1 Characters Per Day........: 180466.9 Average Daily Volume......: 176 kB Total Posters This Week...:...

Tab Index Problem
I am trying to set the tab index values for my form controls. The problem is that even though I enter a certain value for a particular tab index, it is not saved. It seems to be automatically reassigned. I have tried starting from the end and assigning a high number to those controls so that the lower indeces are available. The tab index values for the controls throughout the form are still being reassigned though :( How can I get the index values that I assign to stay? Thanks in advance for your help. Instead of trying to set the tab index, open VIEW > TAB ORDER and drag and drop the ...

Windows Update error 80070103 01-28-10
I installed Creative X-Fi Titaniun audio sound card on my Destop Computer. The drivers on cd that came with this sound card for Vista and XP. I am using Windows 7 and having trouble installing the driver manually. When Windows Update trys to download it, but stops and give me error message "80070103" everytime. Why is it give me error message when there is no driver installed on this computer? Obtain the appropriate driver update (if needed) from the device manufacturer's download page, not Windows Update. TJBuss wrote: > I installed Creative X-Fi Titaniun a...

Outlook Express #10
I need to move all of my Outlook Express e-mails and addresses to my new laptop. I know that with Outlook 2000 you transfer the .pab and .pst files. Does anyone know what the file extension is for Outlook Express? ...

Continue page numbering from previous document while Indexing
Hi! I have a very long document that I have split into several smaller documents to work on and consequently, I have set the page numbering for each subsequent according to the last page of the previous document: "Document #1: Page 1-500" "Document #2: Page 501-750" "Document #3: Page 751-900" However, when I do Indexing in Document #2, regardless of the page number I have set up, it reverts to Page 1 when updating the Index and resets the entire page numbering in that document. How can I 'force' the Indexing to recognize the subsequent pag...

SQL Ethnic Name Matching
I have a table of surnames and ethnicity. I want to choose one ethnicity and the number of characters going backwards from the end that should be matched to approximate ethnicity. This will be used to extract names of one ethnicity form another table. I'll be using MS Access initially, so I assume their character matching routines (those in the help file look like the ones in GW Basic) will do. (Dunno if they exist on other SQL.) Might someone suggest the SQL code for this op? Are LIKE and SIMILAR universal in SQL? I have a mismash of experiences with broad gaps. I used IBM DB2 in...

Manually uninstall GP 10
I am having problems with a GP 10 install on a Vista machine. I tried to do an upgrade from 9 to 10 and it stopped as it was looking for a Service Pack. I have tied to uninstall or reinstall both 9.0 and 10.0 and the setup file will not execute. I have turned off the user account control. I probably have a conflict with something as this a development machine with a myriad of programs on it. It there a way to manually uninstall GP? Jim ...

Installation of Office 2004 on OS X 10.5 Leopard
I installed Office 2004 (and EndNote) after upgrading to Leopard 10.5 and something is amiss. Three (probably related) symptoms I observed: 1. After the install, when opening Word, I'm greeted with the prompt: "An unexpected error occurred while trying to load the Microsoft Framework library." 2. When trying to update Office 2004 via Microsoft AutoUpdate, the application recognizes any out-of-date versions, but then hangs and doesn't download anything (requires a force quit). Also hangs if running updates that were manually downloaded from Microsoft's website. (even o...