 |
 | 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 / October 2006index_match_error!
hi! i am getting #NA error for the following array entered formula! =INDEX(Sheet1!B1:B4,MATCH(A1,LEFT(Sheet1!A1:A4,3),0)) what i am doing wrong? help pl! -via135 Why are you trying to match A1 against the first 3 chars of A1 (and 2 and 3 and 4)? It can only match if A1 is 3 chars.  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > hi! > [quoted text clipped - 8 lines] > > -via135 hi! actually my look-up value A1 (3 characters) is in sheet2 where as the look-up array A1:A4 (4 characters) is in sheet1... and the formula is in B1 of sheet2..! -via135 >Why are you trying to match A1 against the first 3 chars of A1 (and 2 and 3 >and 4)? It can only match if A1 is 3 chars. [quoted text clipped - 4 lines] >> >> -via135 I see. Try this then =INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=A1),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > hi! > [quoted text clipped - 12 lines] > >> > >> -via135 hi! still i am getting the same error #NA -via135 >I see. Try this then > [quoted text clipped - 8 lines] >> >> >> >> -via135 >hi! > [quoted text clipped - 7 lines] >>> >> >>> >> -via135 yes.. it works when the look-up value is text and gives #NA error when the same is a number string! -via135 Another alternative =INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=TEXT(A1,"General")),0)) SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > >hi! > > [quoted text clipped - 14 lines] > > -via135 hi! this one works good when the return value (sheet1!b1:b4) is as long as text. when there is a number in the index array, again i am getting the same error #NA..???!!! -via135 >Another alternative > [quoted text clipped - 5 lines] >> >> -via135 sorry..Bob..! it works correctly. mistake is on my part giving the index array wrong..!! thks for the help! via135 >hi! > [quoted text clipped - 10 lines] >>> >>> -via135 |
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.