 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007if, and match
Hello all. Ok, I have a question about my formula in excel. =if(b1<>1,match(a1,a2:a$300,0),"ok") b1 is a cell that I've used countif in. In this specific case, I'll either have 1, or 2 as my values. What I want to do is to locate the whereabouts of the second value that matches a1, somewhere in column a. This is why I started at the next cell down from my source cell--a1. I.e., I am already aware of the first appearance of the number, I want subsequent appearances, and locations. The problem that I've gotten is that it's not showing the correct cell location, and appears to be some 5 to 140 cells off from the actual location. Here's my understanding of my question. if the b1 cell's value does not equal 1, look throughout the chosen range--a2 through a300-- for the specified cell--a1--value, if it's found, tell me where it is; else if b1 does equal 1, everything is ok. I.e., the matched value shows the location as 168, and is actually in 176 (I used ctrl+f), another one states by match value that it's in 46, and by ctrl+f, I found it in cell 157. I'm not able to readily identify anything that I've done incorrectly here, so a second, or nth pair of eyes would be appreciated. As always, thank you, in advance. The position is relative to the range given to the match function. Are you trying to find the row number the second value is in or the index number within the range containing your data (which appears to start at A1, so these two are probably the same - for now). If the formula you posted returns a 3, that means it is in cell A4 (third cell in the range A2:A300). You could perhaps try: =if(b1<>1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok") I assume your data is sorted and you are trying to find the row number. > Hello all. > Ok, I have a question about my formula in excel. [quoted text clipped - 24 lines] > > As always, thank you, in advance. Row locations are my goal. I've noticed that "match" finds the row that the requested value is located in. Ok, so that I understand this correctly.... Because I'm dropping one row each equation, it "resets" the starting point to be that cell the equation is in? While that kind of makes sense, in this case, I have some answers whose answers should only be one or two row values off. Some of these are upwards of 200 rows short. I.e., my errors are stating that a value is located in row 46, and it's actually in 157, and I'm near the top. I just went back and looked more closely, it does appear that the "reset" occurs; thus making that row's location the starting point. I.e., if my equation is in row 11, then row eleven is my starting point for my search the way it is written. Ok, let me ask this. how can I have it ignore the one row that I'm in with that specific equation? I.e., =if(b12<>1,match(a12, a1:a11;a13:a300,0),"ok") Again, thank you. > The position is relative to the range given to the match function. Are you > trying to find the row number the second value is in or the index number [quoted text clipped - 35 lines] > > > > As always, thank you, in advance. Ok, While I've solved my problem in three steps as opposed to one, I'd still like to know if I can use match and skip the row that my equation is located in. I.e., a jump over as it were. Is that possible, and if so, how? Thank you. > The position is relative to the range given to the match function. Are you > trying to find the row number the second value is in or the index number [quoted text clipped - 35 lines] > > > > As always, thank you, in advance. You could try: =IF((B1>1)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$300,0)+ROWS(A$1:A1),"") which would tell you where the second occurence is (I assume there can't be more because you said the countif function will return either 1 or 2). If you are trying to identify the duplicates, you could enter =Countif(a$1:a1) in cell B1, copy down to B300. Then autofilter on column B (may want to insert a header row) using a custom criteria of greater than 1. That would bunch all of them together for you and you could delete the filtered rows (Edit/Delete Row) - if your intention is to delete them. Just be sure to backup your data before trying new things. > Ok, While I've solved my problem in three steps as opposed to one, I'd still > like to know if I can use match and skip the row that my equation is located [quoted text clipped - 41 lines] > > > > > > As always, thank you, in advance. Ok I've got it now-- thank you. The only difference is that the rows function needs to be row() instead of rows(A$1:A1) Having the row() gives the actual row number, and the rows(A$1:A1) offsets it by a negative one-- making me think it'd read rows(A$1:A1) as 0, then 1, 2, ......, n So, the function then becomes =IF((B1>1)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$300,0)+ROW(),"") ballissimo..... Thank you very much for your help. Enjoy your weekend. > You could try: > =IF((B1>1)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$300,0)+ROWS(A$1:A1),"") [quoted text clipped - 56 lines] > > > > > > > > As always, thank you, in advance. I would suspect some of your cells in column A are formatted as text. Regards, Paul > Hello all. > Ok, I have a question about my formula in excel. [quoted text clipped - 26 lines] > > As always, thank you, in advance. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.