 |
 | 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 / General Excel Questions / September 2007=Index Problem
Hi, I have a spreadsheet with the following Columns: A = Last Names B= First Names C = Extentions E,F,G are blank H = Last Names (only a selection of colum A) I = First Names (the corresponding first names) J = Cell Phone Numbers I have used the following forumla =INDEX($A$1:$J$365,MATCH($A6,$H$1:$H$279),10) I would like it to look in Column H and if it finds the same last name as in Column A put the corresponding cell phone number from Column J into Column L This works so far except if it cannot find a last name in Column H it puts the cell phone number from the line previous until it findsa matching last name again. I would like it just to stay blank if it can't find the matching last name. What can I change in my formula to achieve this? Thanks Hi Nikki, =IF(ISNA(INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)),"",INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)) The above is a tested formula. You need to edit with your ranges. Also it is actually one line but does not fit here. Note the zero parameter at end of match formula. See help for what it does. Regards, OssieMac > Hi, > [quoted text clipped - 22 lines] > > Thanks "OssieMac" <OssieMac@discussions.microsoft.com> wrote... ... >=IF(ISNA(INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)),"", >INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)) ... You could shorten the existence check considerably. =IF(COUNTIF($H$1:$H$30,$A1), INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") or, possibly faster recalculating, =IF(COUNT(MATCH($A1,$H$1:$H$30,0)), INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") Hi, Thanks they all work. Now I have come across the problem that some people have the same last name so I need to change the formula so that it checks First and Last names (Columns A, B with Columns H, I) Any ideas? Regards Nikki > "OssieMac" <OssieMac@discussions.microsoft.com> wrote... > .... [quoted text clipped - 11 lines] > =IF(COUNT(MATCH($A1,$H$1:$H$30,0)), > INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") Change the match part INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") MATCH(1,(H1:H30=A1)*(I1:I30=B1),0) and you need to enter the formula with ctrl + shift & enter  SignatureRegards, Peo Sjoblom > Hi, > [quoted text clipped - 26 lines] >> =IF(COUNT(MATCH($A1,$H$1:$H$30,0)), >> INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") Hi, Thanks they all work. Now I have come across the problem that some people have the same last name so I need to change the formula so that it checks First and Last names (Columns A, B with Columns H, I) Any ideas? Regards Nikki > "OssieMac" <OssieMac@discussions.microsoft.com> wrote... > .... [quoted text clipped - 11 lines] > =IF(COUNT(MATCH($A1,$H$1:$H$30,0)), > INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"") |
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.