Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
 

I have been struggling with how to solve this problem. Sounds simple
verbally but I can't quite get it! I need a formula that will look at 3
columns in an array - if the value in column A and the value in column B are
the same as the values in adjacent columns in a spreadsheet, then delibet the
value in column C?

E.g.;
A B C
1 LEVEL1 LEVEL2_OLD LEVEL2_NEW
2 FINANCE INVEST INVEST1
3 ENGINEERING BUILD BUILD2
4 QUALITY CHECK CHECK1
5 PROPERTY INVEST INVEST2
6 RESEARCH CHECK CHECK2
7
8 ENGINEERING BUILD


This might be the array - the values in A8, B8 might be Engineering and
Build - I need to deliver BUILD2.

I hope this problem isn't as much of a tease for your minds as it is for
mine. Thanks in advance for any suggestions.
--
JT
Sydney, Australia

Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
 

One way....

=INDEX(C1:C6,MATCH(1,(A1:A6=A8)*(B1:B6=B8),0))

confirmed with CTRL+SHIFT+ENTER

"JT" wrote:

I have been struggling with how to solve this problem. Sounds simple
verbally but I can't quite get it! I need a formula that will look at 3
columns in an array - if the value in column A and the value in column B are
the same as the values in adjacent columns in a spreadsheet, then delibet the
value in column C?

E.g.;
A B C
1 LEVEL1 LEVEL2_OLD LEVEL2_NEW
2 FINANCE INVEST INVEST1
3 ENGINEERING BUILD BUILD2
4 QUALITY CHECK CHECK1
5 PROPERTY INVEST INVEST2
6 RESEARCH CHECK CHECK2
7
8 ENGINEERING BUILD


This might be the array - the values in A8, B8 might be Engineering and
Build - I need to deliver BUILD2.

I hope this problem isn't as much of a tease for your minds as it is for
mine. Thanks in advance for any suggestions.
--
JT
Sydney, Australia

Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH'
 

Thanks Daddylonglegs - elegant and solved my problem...
--
JT
Sydney, Australia


"daddylonglegs" wrote:

One way....

=INDEX(C1:C6,MATCH(1,(A1:A6=A8)*(B1:B6=B8),0))

confirmed with CTRL+SHIFT+ENTER

"JT" wrote:

I have been struggling with how to solve this problem. Sounds simple
verbally but I can't quite get it! I need a formula that will look at 3
columns in an array - if the value in column A and the value in column B are
the same as the values in adjacent columns in a spreadsheet, then delibet the
value in column C?

E.g.;
A B C
1 LEVEL1 LEVEL2_OLD LEVEL2_NEW
2 FINANCE INVEST INVEST1
3 ENGINEERING BUILD BUILD2
4 QUALITY CHECK CHECK1
5 PROPERTY INVEST INVEST2
6 RESEARCH CHECK CHECK2
7
8 ENGINEERING BUILD


This might be the array - the values in A8, B8 might be Engineering and
Build - I need to deliver BUILD2.

I hope this problem isn't as much of a tease for your minds as it is for
mine. Thanks in advance for any suggestions.
--
JT
Sydney, Australia