 |
 | 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 / March 2008If with AND - Is this correct?
What is wrong with this formula? =IF(AND('Node 01'!A1:A1000=A22,'Node 01'!B1:B1000=B22),'Node 01'!H1:H1000,0) Looking at "A" and "B" for a match and the anwser is "H" the same row as "A&B" Believe this expression, array-entered with CTRL+SHIFT+ENTER (instead of just pressing ENTER) will return what you're after: =INDEX('Node 01'!H1:H1000,MATCH(1,('Node 01'!A1:A1000=A22)*('Node 01'!B1:B1000=B22),0))  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > What is wrong with this formula? > > =IF(AND('Node 01'!A1:A1000=A22,'Node 01'!B1:B1000=B22),'Node 01'!H1:H1000,0) > > Looking at "A" and "B" for a match and the anwser is "H" the same row as "A&B" It came back with #NA > Believe this expression, array-entered with CTRL+SHIFT+ENTER (instead of just > pressing ENTER) will return what you're after: [quoted text clipped - 5 lines] > > > > Looking at "A" and "B" for a match and the anwser is "H" the same row as "A&B" > It came back with #NA Did you remember to "array-enter" the formula ie to press CTRL+SHIFT+ENTER CSE] to confirm the formula (instead of just pressing ENTER)?? If you did the above confirmation correctly, you should see Excel wrap curly braces: { } around the formula in the formula bar. If you don't see it (the curly braces), click inside the formula bar and try the CSE confirmation again. The visual check on the curly braces is the *only way* (afaik) to check that the formula is correctly array-entered. If the formula is NOT array-entered, it'll return an #N/A, despite there being a obvious match. Here's a quick working sample to prove that it works: http://www.freefilehosting.net/download/3d5cd Array index n match.xls SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- It's a pity that you seem to have dismissed my responses It should work fine SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
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.