If 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"

If with AND - Is this correct?
 

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote:
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"

If with AND - Is this correct?
 

It came back with #NA

"Max" wrote:

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote:
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"

If with AND - Is this correct?
 

"Greg" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If with AND - Is this correct?
 

It's a pity that you seem to have dismissed my responses
It should work fine
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---